Here's how I got MySQL to do some enumerating for me, meaning increment a counter and add it to a select statement as in a numbered list. It is questionable if this type of functionality should be part of the "business" logic, as opposed to the display logic, but still, you never know.
What you need to do in this case is first to define the variable
SET and you assign the default value of 0.
Then you include
@inc as part of your
SELECT statement. You can even use
AS to nickname the variable expression.
Also as part of the
SELECT you take care of incrementing the value in
Here's the thing:
SET @inc :=0; SELECT @inc := @inc + 1 AS a, `some_field` FROM `some_table`;
Tested in MySQL versions (oldest to latest) 4.0.26, 4.1.10, 4.1.15 and 5.0.20
If anyone has an idea how to this in one shot, without executing the
SET first, I'll be very interested. I played with
IF in order to check if
@inc was defined, and if not to define it, but hit a brick wall.
This entry was posted on Monday, May 15th, 2006 and is filed under mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.