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.