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:
MySQL does not have a built in row number function, which makes it an ugly hack to get the row number – an ugly hack that needs to be repeated over and over, every time you need it.
I have taken the liberty of writing a rownum() function, that is just as bad, but at least it will keep your code clean.
DISCLAIMER:
- does not work on older versions of mySQL
- It is based on using variables that only reset when time changes. If the function is invoked in 2 separate places in the same microsecond(1 millionth of a second), the output will not be as expected. The variable will not reset, the output will not start counting from 1.
- If you run the function more than once in the same query, it will increment every time it is run, so possibly multiple times per row.
For the purposes of BI, I find the associated risks, when used as intended, non existent.
For older versions of mySQL server, you can try finding another creative way to reset the counter for the row number.
Row number function
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP FUNCTION IF EXISTS rownum; DELIMITER $$ CREATE FUNCTION rownum() RETURNS int(11) BEGIN set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1); set @ranklastrun=CURTIME(6); RETURN @prvrownum; END $$ $$ DELIMITER ; |
to invoke it, use rownum(), such as below
|
1 2 3 4 |
select cols, rownum() from table |
Good luck!
thank you!