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

    Requires Free Membership to View

    Login

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.

This was first published in August 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top