7

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?

2 Answers 2

6

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
    FROM
      ( 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
        FROM 
            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:

SELECT LastName
     , 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
FROM
    Person p 
ORDER BY 
    LastName
  , FirstName ;

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

4

Possibly not the best performing solution:

SELECT p.*, (SELECT count(*) FROM Persons WHERE LastName > p.LastName) AS Rank
FROM Persons p ORDER BY LastName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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