Get Record Number

I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.

That depends on wheter you are using SQL Server 2005 or any version below. SQL 2k5 introduced the ROWNUMBER() function which will let your create a rownumber according to some rules (only a new number if a grouping changes, etc.) if you are using SQL2k or bwlo you will have to go another way. What are you currently using ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Currently using SQL-2000|||

There is no straight-forward solution in SQL 2000.

Basically, you can create a temp table with an identity column (record number) and insert each row of your table into it. After that, select all the rows in the temp table out with the record number.

You can also only insert the PK column into the temp table and select using a join of the temp table and the original table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.