Home

How to number rows in MySQL

2009, June 7 - 22:17 — müzso
http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/
Xaprb published a nice method for creating sequentially increasing identifiers for groups of records in a query.

Here's the code for reference (check Xaprb's site for more):
set @type = '';
set @num  = 1;

select
   type,
   variety,
   @num := if(@type = type, @num + 1, 1) as row_number,
   @type := type as dummy
from fruits;

+--------+------------+------------+--------+
| type   | variety    | row_number | dummy  |
+--------+------------+------------+--------+
| apple  | fuji       |          1 | apple  |
| apple  | gala       |          2 | apple  |
| apple  | limbertwig |          3 | apple  |
| cherry | bing       |          1 | cherry |
| cherry | chelan     |          2 | cherry |
| orange | navel      |          1 | orange |
| orange | valencia   |          2 | orange |
| pear   | bartlett   |          1 | pear   |
| pear   | bradford   |          2 | pear   |
+--------+------------+------------+--------+

This is just what I needed for inserting a bunch of records into a table that stores a Hibernate collection (that represents a many-to-many relation in my data model). There's an index column in the table of the collection which needs a zero-based sequence for each ID of the master table/entity.

Syndicate content