Home » SQL & PL/SQL » SQL & PL/SQL » using rownum (oracle 10 g)
using rownum [message #657875] Fri, 25 November 2016 02:55 Go to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member

Sir,

help me in writing a query to get the following output


create table test_25112016 (nam varchar2(20),sal number);

insert into test_25112016 values('AAA',4500);
insert into test_25112016 values('AAA',4600);
insert into test_25112016 values('AAA',4700);
insert into test_25112016 values('BBB',100);
insert into test_25112016 values('BBB',200);
insert into test_25112016 values('CCC',200);
insert into test_25112016 values('CCC',4400);
insert into test_25112016 values('CCC',2000)
insert into test_25112016 values('CCC',220);



Expected output is


1 AAA 4500
2 AAA 4600
3 AAA 4700
1 BBB 100
2 BBB 200
1 CCC 200
2 CCC 220
3 CCC 2000
4 CCC 4400
Re: using rownum [message #657876 is a reply to message #657875] Fri, 25 November 2016 02:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far?
Re: using rownum [message #657877 is a reply to message #657876] Fri, 25 November 2016 03:01 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
i tried this simple one but confused in using rownum, should i use a outer query??

select rownum,nam,sal from test_25112016 group by rownum,nam,sal order by nam
Re: using rownum [message #657878 is a reply to message #657875] Fri, 25 November 2016 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and at http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post.

Re: using rownum [message #657882 is a reply to message #657878] Fri, 25 November 2016 03:28 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
thank you for your suggestion, used rank and dense rank but its not satisfying the output , in my output serial number has to start again with the next new name.

Expected output is

srl name sal

1 AAA 4500
2 AAA 4600
3 AAA 4700
1 BBB 100
2 BBB 200
1 CCC 200
2 CCC 220
3 CCC 2000
4 CCC 4400
Re: using rownum [message #657883 is a reply to message #657882] Fri, 25 November 2016 03:30 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Post the what you tired and the results of it. The actual output, in code tags please Smile
Re: using rownum [message #657884 is a reply to message #657883] Fri, 25 November 2016 03:34 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
select 
rank() over (order by nam,sal ) "RANK",
dense_rank() over (order by nam) "DENSE_RANK",
nam,sal 
from test_25112016 


Expected output

srl name sal

1 AAA 4500
2 AAA 4600
3 AAA 4700
1 BBB 100
2 BBB 200
1 CCC 200
2 CCC 220
3 CCC 2000
4 CCC 4400
Re: using rownum [message #657885 is a reply to message #657884] Fri, 25 November 2016 03:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you don't feel confident with analytic functions, you could try it with subqueries. Here's a start:
SELECT test_ordered.nam,
       test_ordered.sal,
       (SELECT Count(*)
        FROM   test_25112016
        WHERE  test_ordered.nam = test_25112016.nam)
FROM   (SELECT nam,
               sal
        FROM   test_25112016
        ORDER  BY nam,
                  sal) test_ordered; 
If I were the teacher, I do not know which solution I would favour.
Re: using rownum [message #657886 is a reply to message #657885] Fri, 25 November 2016 03:38 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Close, you need to tell it what to PARTITION the window by, in your case NAME.
Re: using rownum [message #657887 is a reply to message #657886] Fri, 25 November 2016 03:53 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
After applying partition i got the output, thank you Roachcoach for the hint, john and michel for the help.
John you are always a good teacher for many people who look for solutions.
select 
row_number( ) over (partition by nam order by nam,sal desc) "ROW_NUMBER",
nam,sal 
from test_25112016
Re: using rownum [message #657888 is a reply to message #657887] Fri, 25 November 2016 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You never need to order by column that you are partitioning by. Order is within a given partition and for all the rows in a partition nam will have the same value.
Re: using rownum [message #657889 is a reply to message #657888] Fri, 25 November 2016 04:01 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
ok fine i will take care of this.
thank you for correcting.
Re: using rownum [message #657892 is a reply to message #657888] Fri, 25 November 2016 06:40 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
cookiemonster wrote on Fri, 25 November 2016 04:56
You never need to order by column that you are partitioning by. Order is within a given partition and for all the rows in a partition nam will have the same value.
The row_number analytic has to have an order by, but the select it self doesn't unless you want to distplay the results in a particular order.
Re: using rownum [message #657893 is a reply to message #657892] Fri, 25 November 2016 07:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid you misread what CM has said.

Here:

row_number( ) over (partition by nam order by nam,sal desc)

"ORDER BY NAM" is unnecessary, because it is a column that is used in the "PARTITION BY" clause.
Re: using rownum [message #657894 is a reply to message #657893] Fri, 25 November 2016 07:06 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Oh... lol... you are right. I missed that, sorry Cookiemonster.
Previous Topic: Trigger to prevent update/delete/insert operation
Next Topic: How to generate a insert script as it is what ever we running the insert script?
Goto Forum:
  


Current Time: Thu Mar 28 10:35:56 CDT 2024