Getting a recordID in my SQL select query

Question

I have a simple stored procedure that returns records:

Select empID, EmpFirstName, EmpLastName from EMPLOYEES

The returned result is:

    EmpID         EmpFirstName       EmpLastName  
    -----         ------------       -----------  
   30152          John               Smith  
   30114          Tom                Jones  
   56332          Steve              Williams  
   85442          Paul               Johnson

What I need is:

 RecID       EmpID         EmpFirstName       EmpLastName  
 -----       -----         ------------       -----------  
 1      30152          John               Smith  
 2      30114          Tom                Jones  
 3      56332          Steve              Williams  
 4      85442          Paul               Johnson

How can I get the recordID column?

Thanks

`Select RecID, empID, EmpFirstName, EmpLastName from EMPLOYEES` ? What is the structure of your DB? — Steve Robbins, Aug 09 '11 at 22:31
What is determining the order for the assignment of RecID? Sorted by EmpID, 30114 will have RecID = 1. Does that matter? — Mikael Eriksson, Aug 09 '11 at 22:31
There's a difference between a record 'ID' (i.e., some value stored in your database that positively identifies a record), and a line number (which is just an arbitrary number that will not necessarily identify the same row if you run the query again.) Which is it that you want? — Chains, Aug 09 '11 at 22:38

Abe Miessler · Accepted Answer · 2011-08-09T22:36:02.727

6

Probably want to use Row_Number:

Select ROW_NUMBER() OVER(ORDER BY empID) AS RecId, 
       empID, 
       EmpFirstName, 
       EmpLastName 
from EMPLOYEES

unless there is actually a RecId column in EMPLOYEES in which case it would just be this:

Select RecId, 
       empID, 
       EmpFirstName, 
       EmpLastName 
from EMPLOYEES

edited Aug 09 '11 at 22:36

answered Aug 09 '11 at 22:30

Abe Miessler

82,532
99
305
486

ok, that works. Lets say if there was no empID, what would I use as "Order By" ? – DNR Aug 09 '11 at 22:39
3

You can use any other column in there. – Abe Miessler Aug 09 '11 at 22:42

score 3 · Answer 2 · edited May 23 '17 at 11:44

If you're using at least SQL Server 2005, ROW_NUMBER is what you need.

Example:

Select
    row_number() over (order by empID) as RecID,
    empID, 
    EmpFirstName, 
    EmpLastName
from 
    EMPLOYEES

If you're using SQL Server 2000 or older, it's unfortunately not that easy.
SQL Server 2000 doesn't support ROW_NUMBER, but there are some workarounds to achieve something similar.
All of them seem to have some drawbacks, though.

score 1 · Answer 3 · answered Aug 09 '11 at 22:33

1

SELECT 
    ROW_NUMBER() OVER(ORDER BY empID) as RowId, 
    empID, 
    EmpFirstName, 
    EmpLastName 
FROM EMPLOYEES

More details on ROW_NUMBER()

answered Aug 09 '11 at 22:33

sll

61,540
22
104
156

Getting a recordID in my SQL select query

3 Answers3