Archive for June 13th, 2014

Add Row Numbers to MySQL Results

Sometimes when querying MySQL I like to return a new column containing an ordered numerical sequence for each row in the result set. Here’s an explanation of how to do it.

Screen Shot 2014-06-13 at 08.56.52

I suppose this isn’t really needed in many, if not most, queries, but sometimes it’s just what I want. As an example, I have a users table that contains three columns: id, first_name and last name. The following query will retrieve a result set from this table where the first_name column is equal to John.

SELECT uuid
FROM users
WHERE email LIKE '%j%';

We can easily modify this query to insert an additional column to the front of the result set containing a sequential number for each record. Try this:

SELECT @ROW := @ROW + 1 AS ROW, uuid
FROM users, (SELECT @ROW := 0) r
WHERE email LIKE '%j%';

Voila! And now, how does it work? Let’s start with the FROM clause. We are specifying two sources of data. The first is the users table itself and the second is a subquery that sets a session variable called @ROW and initializes its value to zero. Being a derived table, we must specify an alias, so I used r.

In the SELECT clause, we are incrementing the value of the variable @ROW by one and selecting its value followed by the first_name column from our users table. Since @ROW started life with a value of zero, the first row of the returned result set will be one followed by two, three etc. If we wanted to start at a different value we could’ve set that value in the FROM clause.

And finally, the WHERE clause constrains our results to those whose first name is John. That was pretty easy and quite useful I think.

via Add Row Numbers to MySQL Results | DigitalWindFire.