Pure-SQL Technique for Auto-Numbering Rows in Result Set

Question

I'm looking for a way to sequentially number rows in a result set (not a table). In essence, I'm starting with a query like the following:

SELECT id, name FROM people WHERE name = 'Spiewak'

The ids are obviously not a true sequence (e.g. 1, 2, 3, 4). What I need is another column in the result set which contains these auto-numberings. I'm willing to use a SQL function if I have to, but I would rather do it without using extensions on the ANSI spec.

Platform is MySQL, but the technique should be cross-platform if at all possible (hence the desire to avoid non-standard extensions).

Answer 1

To have a meaningful row number you need to order your results. Then you can do something like this:

SELECT id, name
    , (SELECT COUNT(*) FROM people p2 WHERE name='Spiewak' AND p2.id <= p1.id) AS RowNumber
FROM people p1
WHERE name = 'Spiewak'
ORDER BY id

Note that the WHERE clause of the sub query needs to match the WHERE clause or the primary key from the main query and the ORDER BY of the main query.

SQL Server has the ROW_NUMBER() OVER construct to simplify this, but I don't know if MySQL has anything special to address it.

---

Since my post here was accepted as the answer, I want to also call out Dan Goldstein's response, which is very similar in approach but uses a JOIN instead of a sub query and will often perform better

Answer 2

AFAIK, there's no "standard" way.

MS SQL Server has row_number(), which MySQL has not.

The simplest way to do this in MySQL is

SELECT a.*, @num := @num + 1 b from test a, (SELECT @num := 0) d;

Source: comments in http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/