Labeling Row with Number

Question

Post reply

Labeling Row with Number

CrystalVis

SSC Eights!

Points: 979
More actions
November 9, 2004 at 2:48 pm

#59727

I have no idea how to accomplish this. basically, i have a result set in Query Analyzer. I would like to label each row from 1 up to the last row the requery return. Example:

FirstName LastName Account_Id Line_Number
John          Doe         123              1
Jane          Doe          456             2
.....
Ying           Zest         1023           145

line_number is the column i would like to create as a label for each row in my result set. your help is greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Osoba SSCommitted Points: 1964 More actions · Answer 1

can you live with this?:

select identity(int) as line_number,[column] into #temp from

select * from #temp

drop table #temp

hoo-t SSChampion Points: 12713 More actions · Answer 2

For the second time today, I have to give credit to Frank! I knew that I had seen solutions to this problem on this site, just couldn't remember the solution.

Anyway, take a look at...

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

Steve

CrystalVis SSC Eights! Points: 979 More actions · Answer 3

Thank you all for your input/suggestion.

Osoba,

I'm not ranking on any value so your solution worked perfect!!! Thanks so much!!!

noeld SSC Guru Points: 96590 More actions · Answer 4

Watch out for that Identity Function Solution with select into

IT DOES NOT ALWAYS WORKS !

hungry for why? --> look in here

HTH

* Noel

Michael Girdler Right there with Babe Points: 780 More actions · Answer 5

Found this code, tried it and it worked...

SELECT count(*) RecNum,

a.LastName

FROM Northwind.dbo.Employees a join

Northwind.dbo.Employees b

on a.LastName >= b.LastName

group by a.LastName

order by a.LastName

Url for it: http://databasejournal.com/features/mssql/article.php/10894_2244821_2

Nice run down by Gregory A Larsen

Enjoy

hoo-t SSChampion Points: 12713 More actions · Answer 6

Michael,

I KNEW that I had seen something that was very simple, yet ingenious for this. But I couldn't find it!! That's it! Good job!

Steve

PBirch SSCrazy Eights Points: 8493 More actions · Answer 7

I have found that using the data type "table" can be faster than using temporary tables. I had to write a series of cross-tabs recently. I started out with temporary tables, since I had to add identity and ranks. I then moved to using the data type and there was a obvious improvement.

Quand on parle du loup, on en voit la queue

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Somehow I miss the only "correct" answer to this question:

Do this at the presentational layer of your app! That's not really a job for the server. But (un)fortunately MS thinks different on this. See http://www.winnetmag.com/Windows/Articles/ArticleID/42302/pg/1/1.html for a discussion on the efficiency of this SELECT COUNT(*) approach and what there is to come in the next SQL Server version.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]