Running numbers in SQL

Question

I have a SQL-statement like this:

SELECT name FROM users WHERE deleted = 0;

How can i create a result set with a running number in the first row? So the result would look like this:

1 Name_1
2 Name_3
3 Name_12
4 Name_15
...

Answer 1

In Oracle, SQL Server and PostreSQL 8.4:

SELECT  ROW_NUMBER() OVER (ORDER BY name)
FROM    users
WHERE   deleted = 0

In MySQL:

SELECT  @r := @r + 1 AS rn
FROM    (
        SELECT  @r := 0
        ) vars, users
WHERE   deleted = 0
ORDER BY
        name

In PostgreSQL 8.3:

SELECT  num, (arr[num]).*
FROM    (
        SELECT  generate_series(1, array_upper(arr, 1)) AS num, arr
        FROM    (
                SELECT  ARRAY
                        (
                        SELECT  users
                        FROM    users
                        ORDER BY
                                name
                        ) AS arr
                ) q
        ) q2

Answer 2

In SQL server 2005 you can use row_number() over(order by yourColumn), like this:

SELECT row_number() OVER(ORDER BY myColumn) FROM myTable