sql server - Add a row number to result set of a SQL query

Question

I have a simple select statement. I want to add a temporary column which will number the rows in my result set. I tried this -

declare @num int
set @num = 0;
select t.A, t.B, t.C, (@count + 1) as number
from tableZ as t

It assigns the 1 to all rows. I tried @count = @count + 1 and it did not work. How do I do this thing in a simple manner ?

thanks.

Answer 1

SELECT
    t.A,
    t.B,
    t.C,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS number
FROM tableZ AS t

See working example at SQLFiddle

Of course, you may want to define the row-numbering order – if so, just swap OVER (ORDER BY (SELECT 1)) for, e.g., OVER (ORDER BY t.C), like in a normal ORDER BY clause.