CODEWITHSUNDEEP
sqlsorting
Elmi
Elmi

Reputation: 6193

SQL: get rank of a data set after sorting data

I have a simple table that contains unique identifiers and a counter value for each of them. What I need to get as result is the position of a specific identifier after the table was sorted by the counter values.

My current solution would be to do a SELECT for all data while sorting the result, then going through the result list to find the required identifier, the position where I find it is the ranking value I want to get.

Unfortunately this requires much memory and computing time for large tables. So my question: is there a SQL statement that returns the position of a specific identifier after the table was sorted?

Upvotes: 3

Views: 2827

Answers (1)

marc_s
marc_s

Reputation: 754268

If you're using SQL Server 2005 and newer, you can use the RANK() or DENSE_RANK() windowing functions:

SELECT
   (your list of columns here),
   RANK() OVER(ORDER BY yourcolumnnamehere)
FROM dbo.YourTable

or:

SELECT
   (your list of columns here),
   DENSE_RANK() OVER(ORDER BY yourcolumnnamehere)
FROM dbo.YourTable

See explanation of difference between RANK and DENSE_RANK here

Other RDBMS (like Oracle) also seem to support those windowing functions.

Upvotes: 5

Related Questions