Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

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

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.