UPDATE: On July 18 2018 the MySql server team announced the introduction of window functions. The article was written before this, offering an workaround to gain some similar functionality. I updated this article now with the latest and best practice way to do this correctly, but sill leave the old article for reference.
AS of Mysql 8, you can now use window functions such as rownum() OVER(PARTITION BY country) . Interestingly, you can just reference your partition by an alias, and define the partition in a separate window clause section (like where clause), allowing reuse:
|
1 2 3 4 5 6 |
mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', rank() OVER w AS 'rank' FROM numbers WINDOW w AS (ORDER BY val); |
Happy window aggregating!
OLD ARTICLE FROM 2014 BELOW:
A quick and dirty custom MySQL Rank() function to emulate rank from PostgreSQL into mySQL. Same disclaimers as the custom rownum() function. In this case, I am using it to rank decimals, but feel free to change the input type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP FUNCTION IF EXISTS rank; DELIMITER $$ CREATE FUNCTION rank(input DECIMAL(11,4)) RETURNS int(11) BEGIN set @prvinput=if(@prvinput is null, input, @prvinput); set @prv=if(@ranklastrun=CURTIME(6) and @prvinput=input,@prv,if(@ranklastrun=CURTIME(6), @prv+1, 1)); set @ranklastrun=CURTIME(6); set @prvinput=input; RETURN @prv; END $$ $$ DELIMITER ; |
to invoke, use rank(column to rank),such as below:
|
1 2 3 4 5 6 7 8 9 10 |
select nr, rank(nr) from ( select 2 as nr union all select 2 union all select 2 union all select 3 union all select 34 union all select 4)a order by 1 |
output:
|
1 2 3 4 5 6 7 |
|nr| |rank(nr)| |2| |1| |2| |1| |2| |1| |3| |2| |4| |3| |34| |4| |