Sorting the Table and getting the position

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?

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

StackExchange Database Administrators Q#18316, answer score: 6