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

There is no ANSI-standard way to do this of which I am aware.

In SQL Server you have a ROW_NUMBER() function which can be used and in Oracle, there is a ROWNUM pseudo column.

In MySQL, there is this technique

Answer 2

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 3

This page should give you a standard SQL way of doing it:

http://www.sqlteam.com/article/returning-a-row-number-in-a-query

Hope this helps.

Answer 4

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/

Answer 5

One idea that is pretty inefficient but is ANSI SQL would be to count the number of rows with a lesser id matching the same criteria. I haven't tested this SQL and it probably won't work, but something like:

SELECT id, name, sub.lcount
FROM people outer
JOIN (SELECT id, COUNT(id) lcount FROM people WHERE name = 'Spiewak' AND id < outer.id GROUP BY id) sub on outer.id = sub.id
WHERE name = 'Spiewak'

Answer 6

For SQL server, check out the RANK function.

Answer 7

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo