How can I return Serial number in resultset using SELECT statement?

This is very easy in SQLServer2005/2008 as there is new function Row_number() available for the same.



--Query for SQL2005/2008
Select Row_Number() over (order by emp_id) AS [Sr. No.], * from myTable


But there is some tricky way while using SQl2000, I prefer to use following query


--Query for SQL2000
SELECT (SELECT COUNT(*) FROM myTable tbl WHERE tbl.id <= mTbl.id) AS rownumber,*
FROM myTable mTbl ORDER BY id


Note: You can change myTable with your table and field id with your table's field.


Article by Gaurav Aroraa
Gaurav is a Microsoft Technology Specialist professional. He has awarded lifetime membership from Computer Society of India (CSI). He has more than 13yrs of experience in the industry. Currently, he is working in the capacity of Solution Architect with an MNC. He is serving to the various communities since 1999.

Follow Gaurav Aroraa or read 157 articles authored by Gaurav Aroraa

Comments

Author: Mrs. Meetu Choudhary Nanda23 May 2009 Member Level: Gold   Points : 0

a very good code snippet

Author: Shuby Arora24 May 2009 Member Level: Gold   Points : 0

A good one, I really need to understand it



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: