display row number like excel

Question

display row number like excel

Cindy Hallum

SSCommitted

Points: 1808
More actions
August 6, 2007 at 3:14 pm

#373133

I thought for sure that SQL Server 2005 had a row count function that displays the row number for each row in a select statement. Now I cannot find anything anywhere about it. Am I nuts, or what?

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

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

Olga B SSCommitted Points: 1529 More actions · Answer 1

I didn't Google much on the subject, assuming that you probably did but I did rifle through BOL and came up empty (or nuts).

The best I could come up with was to create a temp table with an identity column and to insert the results of your select into it, thus populating the identity column with row numbers.

As in:

CREATE TABLE #Cl (

RowNum int IDENTITY(1,1) NOT NULL,

ClientID int ,

ClientName varchar(50) )

GO

insert into #Cl

select ClientID, ClientName from Client

select * from #Cl

I suspect there's a better way, though, so I shall wait to be enlightened.

Cindy Hallum SSCommitted Points: 1808 More actions · Answer 2

I figured it out:

SELECT CODE, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY CODE) AS [ROWNUMBER] FROM D3CUSTOMERS

But I am glad to hear you didn't see it in BOL either. At least I'm not going crazy.

Thanks,

Cindy

Olga B SSCommitted Points: 1529 More actions · Answer 3

But looks like I am. How on Earth did I miss it?

Anyway, thanks for sharing the solution.

Tim Mitchell SSCoach Points: 15685 More actions · Answer 4

The new row numbering feature in SQL Server 2005 is very handy. Just make sure that anyone who uses the data understands that it is only a row number and is not a distinct identifier.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

wutang SSC-Addicted Points: 409 More actions · Answer 5

wouldn't every row number be distinct ?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

The row number would be, but can in no way be tracked back to a certain record in the database. i.e. it is in no way a primary key for the table.

Tim Mitchell SSCoach Points: 15685 More actions · Answer 7

Yes, every number would be distinct within a given set of data, but a number does not "belong" to a particular row. If you pull the same query later, the data rows will likely not have the same row ids as before. May be a nonissue depending on your use of the data, but just be aware that it could cause confusion (I've seen it happen before).

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices