Enumerating with MySQL

May 15th, 2006. Tagged: mysql

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.

Tell your friends about this post: Facebook, Twitter, Google+

4 Responses

  1. hello there,
    i am trying to reproduce a data server that a company uses to present information to its customers via the web. No problem. What they are using right now is Microsoft Access, connecting via ODBC to a server on line. The cool thing that they can do is have several tables linked together. Like if a certain field is updated in one table, it updates the same info automatically in another table. So, i want to reproduce this in MySQL, but i can’t seem to find out how. Is there a way to pull this off on the server side ? or does that have to be done on the client side ?
    thanks for any tips.

  2. This is how you would do the above without using the set:

    SELECT
    @inc := @inc + 1 AS a,
    `some_field`
    FROM
    (SELECT @inc :=0) temp,
    `some_table`

  3. Very nice, Tom, thanks!

  4. Tom, Thanks a lot!
    i see construction “(SELECT @inc :=0) temp” at first time but i already like it

Leave a Reply