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.
a very good code snippet