How to reference current record position with TSQL

[MMund]

Being a newbie to the TSQL world, I just need to know if there is a way to determine the current record position in a table with TSQL, ala the RECNO() function I used in FoxPro.

Thanks,

Mike

 

[memdiscount]

Hi;

I hope this FAQ will help you from tek-tips.

http://www.tek-tips.com/faqs.cfm?fid=3021

Thanks

Essa Mughal
Toronto, Canada

 

[memdiscount]

Hi;

If you are using SQL Server 2005; two ways, by using the Row_Number() and the Rank() Function.

Example:

-- using Row_Number()

SELECT Row_Number() OVER (order by CustomerID asc) as RowNumber
, CustomerName
FROM Customers

-- using Rank() [almost the same as row_number]

SELECT Rank() OVER (order by CustomerID asc) as RowNumber
, CustomerName
FROM Customers

Thanks

Essa Mughal
Toronto, Canada

 

[SQLSister]

One thing you have to understand about SQL Server is that the records are not guaranteed to have the same order when you query them, therefore no real record number. If you use an identity field on insert you will know what order the records were inserted in, other wise the rank will depend on how you ordered the table and if you do not use an order by clause then the order will not necessarily be the order the records were inserted in.

"NOTHING is more important in a database than integrity." ESquared