Sorting the Table and getting the position

Question

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?

Answer 1

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

Answer 2

Possibly not the best performing solution:

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