May 6, 2004 at 1:15 am
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
May 6, 2004 at 1:22 am
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]
May 7, 2004 at 9:30 am
use northwind
go
select
(select count(1) from customers cu where cu.CustomerId <= c.CustomerId)
as Counter,
* from customers c
order by CustomerId
May 7, 2004 at 10:37 am
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
May 7, 2004 at 11:41 am
If you want soemthing like rownum, ad an identity column.
Michael R. Schmidt
Developer
May 8, 2004 at 9:20 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply