using rownum [message #657875] |
Fri, 25 November 2016 02:55 |
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 #657877 is a reply to message #657876] |
Fri, 25 November 2016 03:01 |
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 #657882 is a reply to message #657878] |
Fri, 25 November 2016 03:28 |
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 #657884 is a reply to message #657883] |
Fri, 25 November 2016 03:34 |
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 |
John Watson
Messages: 8951 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 #657887 is a reply to message #657886] |
Fri, 25 November 2016 03:53 |
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 |
cookiemonster
Messages: 13943 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 #657892 is a reply to message #657888] |
Fri, 25 November 2016 06:40 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
cookiemonster wrote on Fri, 25 November 2016 04:56You 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.
|
|
|
|
|