Auto-increment subquery in tsql select statement
18,577
Solution 1
Assuming you're using SQL 2005 or later:
SELECT dbo.a, dbo.b, dbo.c, ROW_NUMBER() OVER(ORDER BY GETDATE()) AS d
to order the rows as they are returned form the DB. If you want to specify an order you can do so:
SELECT dbo.a, dbo.b, dbo.c, ROW_NUMBER() OVER(ORDER BY dbo.a) AS d
For SQL 2000 and earlier you need a unique value to order by:
SELECT dbo.a, dbo.b, dbo.c, (SELECT COUNT(*) FROM dbo d2 WHERE d2.a <= dbo.a) AS d
FROM dbo
or if you don't need a single SELECT:
SELECT IDENTITY(int,1,1) ID, dbo.a, dbo.b, dbo.c
INTO #Temp
FROM dbo
SELECT * FROM #Temp
Solution 2
I think you can pull it off with something like:
select dbo.a, dbo.b, dbo.c, ROW_NUMBER() OVER (order by dbo.a) as d from somewhere;
My answer assumed SQL Server 2005+, I think D Stanley answer will help you in 2000.
Related videos on Youtube
Author by
tdjfdjdj
Updated on June 04, 2022Comments
-
tdjfdjdj almost 2 years
I have a T-SQL select statement and I want to auto-increment a column in it (that doesn't exist in the database)
select dbo.a, dbo.b, dbo.c, select @d:=1; @increment:=@increment+1 AS d
Is this possible?
-
tdjfdjdj over 12 yearsI have SQL2000. Any other way?