3

Need to rank the below by salary, with highest salary having rank 1.

The RANK column shown is what I'm after:

Empname        sal      address           RANK
----------------------------------------------
Ram            3411     45,east road      2
Anirban        2311     34,west wind      4
Sagor          10000    34,south          1
Manisha        3111     12,d.h road       3
APC
  • 144,005
  • 19
  • 170
  • 281
anirban
  • 51
  • 1
  • 2
  • 4
    Don't type in all caps and include some line breaks. You know, the editor shows a preview of what its going to look like... – rfusca Jul 31 '10 at 19:44

3 Answers3

4

Oracle10g means you can use analytic/ranking/windowing functions like ROW_NUMBER:

SELECT t.empname,
       t.sal,
       t.address,
       ROW_NUMBER() OVER (ORDER BY t.sal DESC) AS RANK
  FROM TABLE t

For the pedantic, replace ROW_NUMBER with DENSE_RANK if you want to see ties get the same rank value:

If two employees had the same salary, the RANK function would return the same rank for both employees. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the dense_rank function which generates consecutive rankings.

The old school means of ranking is to use:

SELECT t.empname,
       t.sal,
       t.address,
       (SELECT COUNT(*)
          FROM TABLE x 
         WHERE x.sal <= t.sal) AS RANK
  FROM TABLE t

The output will match the DENSE_RANK output -- ties will have the same rank value, while being numbered consecutively.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 2
    You may want to change your answer to use RANK() instead of ROW_NUMBER(). RANK() returns ties, whereas ROW_NUMBER() arbitrarily orders ties with unique numbers. – Dave Markle Jul 31 '10 at 20:03
  • Where's the requirement to handle ties? Fact is, the `SELECT COUNT(*)` subselect approach will give ties the same rank value. – OMG Ponies Dec 24 '10 at 06:27
1

Take a look at rank - samples here.

Will A
  • 24,780
  • 5
  • 50
  • 61
1

I often refer to this detailed, informative yet quick link Analytical Functions for the analytical functions.

Piyush Mattoo
  • 15,454
  • 6
  • 47
  • 56