CODEWITHSUNDEEP
sqlsql-server
TCM
TCM

Reputation: 16900

Include row number in query result (SQL Server)

I think each row in sql server is given a unique number. How can I include that in my SQL query results?

Upvotes: 13

Views: 78775

Answers (3)

Montu Padmani
Montu Padmani

Reputation: 1

I think you should use this one....

DCount('Column_Name','Table_Name','Column_Name <= ' & [Column_Name]) as SR

Upvotes: -1

simplyharsh
simplyharsh

Reputation: 36373

The uniqueness in your result will always be your primary key.

But there is something called ROW_NUMBER function that can be used as unique identity of row in a specific result.

SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode;

Upvotes: 3

Thomas
Thomas

Reputation: 64645

If you are referring to the row number provided by Management Studio when you run a query, there is no way to get that because it does not really exist. Management Studio generates that on the fly. You can however, recreate a sequential number using the ROW_NUMBER ranking function if you are using SQL Server 2005 or later. Note you should never assume the database will return the rows in a specified order unless you include an Order By statement. So your query might look like:

Select ....
    , Row_Number() Over ( Order By T.SomeColumn ) As Num
From Table As T
Order By T.SomeColumn

The Order By in the Over clause is used to determine the order for creating the sequential numbers. The Order By clause at the end of the query is used to determine the order of the rows in the output (i.e. the order for the sequence number and the order of the rows can be different).

Upvotes: 38

Related Questions