Take the 2-minute tour ×

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

2 Answers 2

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


