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: 15681 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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | 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: 15681 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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices