How to produce rank in Oracle

Question

Need to rank the below by salary, with highest salary having rank 1. 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

Take a look at rank - samples here.

Answer 2

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

The old school way to do it is:

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