Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ROWNUM in MS SQL Server?


ROWNUM in MS SQL Server?

Author
Message
kaeg
kaeg
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
In Oracle, I can use RowNum like this

SQL> select rownum, product_id from products;

ROWNUM PRODUCT_ID
--------- ----------
1 PROD000003
2 PROD000001
3 PROD000002

Do we have something similar in SQL Server?
TIA

Tim K.



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36232 Visits: 18751
No, you have to do some convulted SQL to make this work.



Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
kaeg
kaeg
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
Thank you, Steve.

So life is not that e-z with SQL Server, huh?



Edited by - kaeg on 08/24/2003 11:35:06 PM



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36232 Visits: 18751
Some things work better, some don't. Haven't seen the need for the row number. Why do you need it?

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ckempste
ckempste
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 1
Hi there

There is no equivalent. Here are some methods from other posters:

SELECT *
FROM
(
SELECT TOP 3000 *
FROM
)
SELECT TOP 5000 *
FROM table
ORDER BY key
) AS top5000
ORDER BY key DESC
) AS last3000
ORDER BY key

Alternatively you can use a temp table with an generated identity
column:

SELECT top5000.*,
rownum = IDENTITY (INT,1,1)
INTO #top5000
FROM
(
SELECT TOP 5000 *
FROM table
ORDER BY key
) AS top5000

SELECT *
FROM #top5000
WHERE rownum BETWEEN 2000 AND 5000


Also, have a good read of this:

http://groups.google.com/groups?oi=djq&selm=an_527662542

Cheers

Ck


Chris Kempster
www.chriskempster.com
Author of "SQL Server 2k for the Oracle DBA"


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
kaeg
kaeg
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
Thank you both again.

Here's what I used before posting the question here.

select (
select count(*)from products
as temp
where convert(int,(right(temp.product_id,6)))<
convert(int,(right(products.product_id,6)))
)+1 as no, product_id
from products

I was looking for something else simpler than that. The subqueries will work if we have a field can be compared or we will end up with creating a temp table like you said.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search