Add a row number to result set of a SQL query
SqlSql ServerSql Server-2005Sql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A
is the single-column clustering key for t
:
SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
FROM dbo.tableZ AS t
ORDER BY t.A;
If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:
ROW_NUMBER() OVER (ORDER BY @@SPID)
-- or for serial plans
ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)
Little tricks I picked up from Paul White in this article (see "Paul's Solution").
Not sure what the variables in your question are supposed to represent (they don't match).