Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am able to sort a table using the following code:SELECT * FROM Persons ORDER BY LastName

But I wanted to get the ranking/position of a person using that order.

For example when I sort Person table by age, I want to get the rank/position of a person named Antinio Trias.

How will I do it?

share|improve this question

Analytic functions have not been implemented in MySQL yet. There are some ways to overcome this limitation.

  • Joining the table to itself using not equality but > or >= and then using GROUP BY and COUNT(*) (what @deszo is essentially doing - a self LEFT JOIN). Downside is that it may not be fast enough, especially if you are self-joining a complex query/view.

  • Using MySQL variables (I should add a huge Warning though: using MySQL variables this way may be broken in future MySQL releases. This is not a guaranteed behaviour):

    SELECT LastName
         , FirstName
         , RowNumber
         , Rank
         , DenseRank
         , RowNumber_OverPartitionBy
      ( SELECT p.*,
               @rown := @rown + 1 AS RowNumber
             , @rnk :=  CASE WHEN LastName = @prev_lastname
                          THEN @rnk
                          ELSE @rown 
                        END AS Rank
             , @drnk := CASE WHEN LastName = @prev_lastname
                          THEN @drnk
                          ELSE @drnk + 1 
                        END AS DenseRank
             , @rowp := CASE WHEN LastName = @prev_lastname
                          THEN @rowp + 1
                          ELSE 1 
                        END AS RowNumber_OverPartitionBy
             , @prev_lastname := LastName
            Person p 
          CROSS JOIN
            ( SELECT @rown := 0, @rnk := 0
                   , @drnk := 0, @rowp := 0
            ) AS dummy 
        ORDER BY LastName
               , FirstName            
      ) AS p 
    ORDER BY LastName
           , FirstName ;

You can test in SQL-Fiddle: test-1

In other DBMS, that have window (analytic) functions, you could have the same in a much more compact query:

     , FirstName
     , Row_Number() OVER(ORDER BY LastName, FirstName)
         AS RowNumber
     , Rank() OVER(ORDER BY LastName)
         AS Rank
     , Dense_Rank() OVER(ORDER BY LastName)
         AS DenseRank
     , Row_Number() OVER( PARTITION BY LastName
                          ORDER BY FirstName)
         AS RowNumber_OverPartitionBy
    Person p 
  , FirstName ;

Test in SQL-Fiddle (SQL-Server, Postgres, Oracle): test-2

share|improve this answer

Possibly not the best performing solution:

SELECT p.*, (SELECT count(*) FROM Persons WHERE LastName > p.LastName) AS Rank
FROM Persons p ORDER BY LastName
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.