How to getRecordCount (Like Sr. No.) in a selectCLause Query in Sql Seevr 2000

Question

How to getRecordCount (Like Sr. No.) in a selectCLause Query in Sql Seevr 2000

Ripal Patel

SSChasing Mays

Points: 629
More actions
April 17, 2008 at 6:07 am

#74891

Hi,
I had a Following simple Select Query in Sql Seevr 2000.
Select City_Name From City_Master
It return RowSet Like
CityName
-------------
Ahmedabad
Delhi
Mumbai
Banglore
CityName Sr. No
-----------------------------
Ahmedabad 1
Delhi 2
Mumbai 3
Banglore 4
Means Ahmedabad is at first row so i want 1, same for Delhi ,Mumbai ,Banglore Are at Second, third,four row so i want 2,3,4 respectively..
i don't wnat to use SELECT statement with INTO clause.
Thanks in Advance...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What defines your ordering?

Please post SQL 2000 related questions in the SQL 2000 forums. Posting in the 2005 forums means you'll probably get answers that use SQL 2005-specific features.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You want to use the Row_Number() function.

Select

cityName,

Row_number() Over(order by city)

From

CityMaster

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Sorry I did not notice that you said SQL Server 2000 since the post is in a 2005 forum until after I posted and Gail had commented.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No replies here please.

Question has been reposted in the sQL 2000 forums

http://www.sqlservercentral.com/Forums/Topic486341-8-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass