select * from (select sum(dpt_no) as numbers from department group by dpt_name) order by numbers desc
Result:
NUMBERS
----------
420
209
106
105
103
102
How to give ranks to this result?
Asked
Active
Viewed 800 times
0
ram12393
- 1,284
- 3
- 14
- 29
-
Are you looking for sth like that [link](http://psoug.org/definition/RANK.htm) – mic4ael Dec 07 '13 at 10:36
-
already i saw that,but how to give rank for my case? – ram12393 Dec 07 '13 at 10:38
1 Answers
2
Please try:
select
numbers,
rank() over(order by numbers desc) Rank
from (
select sum(dpt_no) as numbers
from department group by dpt_name
)
order by numbers desc
sample:
select
Col,
rank() over(order by Col desc) Rank
From YourTable
TechDo
- 18,398
- 3
- 51
- 64
-
can you tell one thing.What is the major difference between RANK and DENSE_RANK? – ram12393 Dec 07 '13 at 10:43
-
Plz check the links http://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php – TechDo Dec 07 '13 at 10:46
-
RANK allows gaps if any rows tie in value - e.g. 1,2,2,4,5; DENSE_RANK does not include any gaps - e.g. 1,2,2,3,4; ROW_NUMBER, on the other hand, resolves ties nondeterministically, e.g. 1,2,3,4,5 – Jeffrey Kemp Dec 13 '13 at 06:15