Get the Rownumber in a SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get the Rownumber in a SELECT statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-14 : 10:46:39

R Guy writes "I've been looking unsuccessfullly for a while on how to get the row number of a record in a recordset using a SQL query. I don't want to use a Temporary table, stored procedure, or cursor. The solution seems to be a variable like @@RowCount, but no luck there.

Thanks for any help."

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 11:28:54

try @@identity

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 11:44:27

quote:

R Guy writes "I've been looking unsuccessfullly for a while on how to get the row number of a record in a recordset using a SQL query. I don't want to use a Temporary table, stored procedure, or cursor. The solution seems to be a variable like @@RowCount, but no luck there.

Thanks for any help."

@@identity will return the last-inserted identity value. This may or may not be what you are looking for.

You are going to need to give some more deets here. There is no row number property naturally supplied in a select statement. You have to put it there. This is a pretty good article describing some techinques on how to 'put it there'.

Personally I am a big fan of


select
   identity(int,1,1) as rownumber,
   <cols>
into
    #<temptablename>
from
    <sourcetable>
order by
    <cols>

<O>

howyue
Starting Member

2 Posts

Posted - 2006-01-23 : 06:04:06

Page47:
your solution is just simlpy brilliant. that is the most effective and simplest way i could found on the internet for this sort of problem.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 06:54:27

quote:
Originally posted by AskSQLTeam

R Guy writes "I've been looking unsuccessfullly for a while on how to get the row number of a record in a recordset using a SQL query. I don't want to use a Temporary table, stored procedure, or cursor. The solution seems to be a variable like @@RowCount, but no luck there.

Thanks for any help."

If you want to get it without Temporary table, a simple way is to have identity column in your table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources