I recently had the need to SELECT some data from MySQL tables and include a “rownum” column on each row. For example, I needed some results that looked like this:
Notice that I cannot use the “id” column because it is auto-generated, and the data is not inserted into the table in the order I am SELECTing it. This type of thing is very easy to achieve in Oracle, which has a built-in “rownum” column that can be added to the SELECT statement. MySQL, unfortunately, does not have a similar construct. So what are we to do?
My first attempt to solve this problem came from a couple of sources like this one: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/.
This solution is quite brilliant. It uses a user defined variable that is incremented each time a row is processed. My query would…
Ver la entrada original 525 palabras más