Press "Enter" to skip to content

Assigning Ranking Values to Rows in a Query Result in SQL (Oracle)

Assigning Ranking Values to Rows in a Query Result in SQL (Oracle)

Problem
The results from a query need to be allocated an ordinal number representing their positions in the
result. You do not want to have to insert and track these numbers in the source data.
Solution
Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. RANK is
applied as a normal OLAP-style function to a column or derived expression. For the purposes of this
recipe, we’ll assume that the business would like to rank employees by salary, from highest-paid down.
The following SELECT statement uses the rank function to assign these values.

select employee_id, salary, rank() over (order by salary desc) as Salary_Rank
from hr.employees;

Our query produces results from the highest earner at 24000 per month, right down to the employee
in 107th place earning 2100 per month, as these abridged results show.

EMPLOYEE_ID SALARY SALARY_RANK
———– ———- ———–
100 24000 1
101 17000 2
102 17000 2
145 14000 4
146 13500 5
201 13000 6
205 12000 7
108 12000 7
147 12000 7

132 2100 107
107 rows selected.