Running numbers in SQL

Question

I have an SQL statement like this:

SELECT name FROM users WHERE deleted = 0;

How to create a result set with a running number in the first row? Thus, the result will look like this:

1 Name_1
2 Name_3
3 Name_12
4 Name_15
...

Answer 1

In Oracle, SQL Serverand 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), for example:

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

Answer 3

- Oracle rownum select. SQL, - .

mysql:

http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/

SQL Server

http://samsudeenb.blogspot.com/2008/09/how-to-generate-rownum-in-sql-server.html