quinta-feira, 24 de julho de 2014

Row number in query in MySQL or MariaDB

In SQL SERVER 2005 or later, there is the "row_number() over (order by <column>)" command to generate a column with the row within your dataset. That's great, but, is there such a feature in MySQL?

The answer is NO! (MySQL and MariaDB team...are you reading this?...hope you are...)

Well anyway... as in almost everything related to programming and technology, there is a workaround (or a jerryrig, a McGyver...etc). To achieve this desired result, you can do an iterating column using a variable:

SELECT 
@i:=@i+1 AS rownum, t.*
FROM tbcustomers t,(SELECT @i:=0) bla

tbcustomer is the table you want to retrieve.

The ROW_NUMBER can do a lot more stuff, like generating the row number in a reverse order based on a colunm ordenation. Wouldnt that be a goodie to have in MySQL/MariaDB (helloooo???...).

Anyway, this is really useful in many situations, like when you need to find the gaps from a increment column due to exclusion, or any other problem you can come up with.

Hope you can use that too!

--
Check out my new book on MySQL 5.6 http://www.amazon.com/dp/B00KSMWLH0

Nenhum comentário:

Postar um comentário

Leave your comment here!