Ranking within a group
How can I get the rank of a certain column without using the rank() function, just using a simple SQL query?
How can I get the rank of a certain column without using the rank() function, just using a simple SQL query?
For example, I have a table named emp_test where I have salary and deptno columns:
salary deptno 10000 10 20000 10 30000 20 40000 10 50000 20
I want the output as:
deptno salary rank 10 40000 1 10 20000 2 10 10000 3 20 50000 1 20 30000 2
Like this:
select deptno
, salary
, ( select count(*) + 1
from emp_test
where deptno = x.deptno
and salary > x.salary ) as rank
from emp_test as x
order
by deptno
, salary desc
The subquery is called a scalar subquery because it returns only one value for each row. It's also a correlated subquery, using the correlation variable x. In effect, this is equivalent to grouping, because the count of the number of rows with a higher salary is determined only within each group, the same department.
Any time you're dealing with ranking, you always want to make sure you've covered what happens with ties. Here is some more data to add to yours:
deptno salary 10 10000 10 10000 10 9370 20 50000
Now your results should look like this:
deptno salary rank 10 40000 1 10 20000 2 10 10000 3 10 10000 3 10 10000 3 10 9370 6 20 50000 1 20 50000 1 20 30000 3
Please make sure you understand why.
Start the conversation
0 comments