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
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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading