How do I display row number with records?

Question

How do I display row number with records?

lucky-80472

SSCertifiable

Points: 7793
More actions
May 6, 2004 at 1:15 am

#107658

Hi,
I want to know the process for displaying the row numbers with records that are being displayed in a sql query.
Are there any pseudocolumns in SQL Server 2000, like those available in Oracle (rownum etc).
Thanks in advance.

Lucky

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

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

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

There is no such thing as rownum in SQL Server. If you're after some sequential numbering this one might be interesting.

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

heber Old Hand Points: 350 More actions · Answer 2

use northwind

go

select

(select count(1) from customers cu where cu.CustomerId <= c.CustomerId)

as Counter,

* from customers c

order by CustomerId

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

heber's example is really cool, but its important to realize that the counter is not a physical row number. Frank's right, storage order in a MSSQL table is completely arbitrary, there's no such thing as a row number. If you want to retrieve the data in a particular order, you must use the ORDER BY clause, otherwise you may get the data back in a different order the next time you run your query.

Steve

Michael R Schmidt Mr or Mrs. 500 Points: 532 More actions · Answer 4

If you want soemthing like rownum, ad an identity column.

Michael R. Schmidt
Developer

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

Heber!

I've been looking for a really simple way to "add a running number" to a SELECT for a long time! Thank YOU! That IS a really neat trick!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)