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 @inc using 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 @inc.
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 IFNULL and IF in order to check if @inc was defined, and if not to define it, but hit a brick wall.
Comments? Find me on BlueSky, Mastodon, LinkedIn, Threads, Twitter
