How to produce rank in Oracle

Question

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

Answer 1

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.