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