Running numbers in SQL
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 开发者_StackOverflowName_1
2 Name_3
3 Name_12
4 Name_15
...
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
In SQL server 2005 you can use row_number() over(order by yourColumn), like this:
SELECT row_number() OVER(ORDER BY myColumn) FROM myTable
It depends on your DBMS -- in Oracle, you can use rownum in the select. There is no standard SQL way, but most DBMS's support some way to do it.
Here's mysql:
http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/
Here's SQL Server
http://samsudeenb.blogspot.com/2008/09/how-to-generate-rownum-in-sql-server.html
精彩评论