Row number in MySQL September 10, 2012

I’ve faced recently with a problem to update positions of accounts ordered by some criteria right in MySQL database. I needed it for accounts rating so I will know in what rating position each account is placed on in the current time. And it reminded me ROW_NUMBER() in MS SQL Server which isn’t available unfortunately in MySQL. Of course we can use procedures and cursors in MySQL though the performance isn’t good at all. And of course there is more nice solution for it – using variable in SQL query. Let’s check it:

SET @row_num = 0;
SELECT @row_num := @row_num + 1 as row_number, id FROM accounts ORDER BY yield DESC;

So we have variable counter right in our SQL query and that’s cool. What if we what to make this shorter? In just one SQL sentence. Here you are:

SELECT @row_num := @row_num + 1 as row_number, a.id FROM accounts a, (SELECT @row_num := 0) as r ORDER BY a.yield DESC;

What we did is just placed @row_num initialisation as sub query. Next I needed to save new account positions in accounts table so I will know what position each account is placed on. And we can do this all in the same query!

UPDATE accounts x
INNER JOIN (SELECT @row_num := @row_num + 1 as row_number, a.id FROM accounts a, (SELECT @row_num := 0) as r ORDER BY a.yield DESC) y ON x.id = y.id
SET x.position = y.row_number;

In summary this query do ranking accounts by yield and save each account position in accounts table in position column. And no cursors or other loops are required!

You can notice that row number will stop working in correct order if you use joins in your SQL query. For instance, we need to join accounts and clients tables and still return rank:

SELECT @row_num := @row_num + 1 as row_number, a.id FROM accounts a
INNER JOIN clients c ON c.id = a.client_id, (SELECT @row_num := 0) as r
WHERE c.`status` = 'active'
ORDER BY a.yield DESC

This will show row_number out of order. I guess because order is made after joining tables and calculating row_number variable. Well it’s easy to fix with sub-query:

SELECT @row_num := @row_num + 1 as row_number, id
FROM accounts, (SELECT @row_num := 0) as r
WHERE id IN (SELECT a.id FROM accounts a INNER JOIN clients c ON c.id = a.client_id WHERE c.`status` = 'active')
ORDER BY yield DESC

 
Leave a Reply