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

Posted Date: 22 May 2009	Resource Type: Code Snippets	Category: SQL
Author: Gaurav Arora	Member Level: Diamond
Rating:	Points: 10

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.

Responses

Author: Miss Meetu Choudhary 23 May 2009 Member Level: Diamond Points : 0

a very good code snippet

Author: Shuby Arora 24 May 2009 Member Level: Gold Points : 0

A good one, I really need to understand it

Feedbacks

Popular Tags

What are tags ?

Search Tags

Sql2008 . SQL2005 . Sql2000 . Serial Number . Select . Row_number . Return . MSSQL . G_arora .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Add Pad side Zero.
	Previous Resource: Handling error 1702
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL

Post resources and earn money!

More Resources