Q

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.

This was first published in August 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close