9

Possible Duplicate:
Add row number to this T-SQL query

Im using sql server 2008.

When I type: select * from Employee. The result like this:

EmpID    |    EmpName    |    Salary
-------------------------------------
DB1608   |    David      |    100000
JT2607   |    John       |    150000
AM1707   |    Ann        |    140000
ML1211   |    Mary       |    125000

But I want the result like this:

No      |    EmpID    |    EmpName    |    Salary
--------------------------------------------------
1       |    DB1608   |    David      |    100000
2       |    JT2607   |    John       |    150000
3       |    AM1707   |    Ann        |    140000
4       |    ML1211   |    Mary       |    125000

The column "No" is the auto increment number, and NOT the identity field in this table.

How can I do this?

Community
  • 1
  • 1
furyfish
  • 2,055
  • 5
  • 26
  • 28
  • Take a look at [ROW_NUMBER()](http://msdn.microsoft.com/en-us/library/ms186734.aspx) - however you will want to find a reliable and predictable column or expression to order by, otherwise the row numbers will be applied in an arbitrary way. – Aaron Bertrand Nov 28 '12 at 02:57

1 Answers1

17

Using ROW_NUMBER() (documentation)

SELECT ROW_NUMBER() OVER (ORDER BY EmpID ASC) AS No, 
    EmpID, EmpName, Salary
FROM Employee

See in action

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • 1
    This is what I was referring to in my comment - without confirmation that it doesn't matter, your ROW_NUMBER() will be applied in a different order than the desired output listed in the question. – Aaron Bertrand Nov 28 '12 at 03:01