Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ROWNUM in MS SQL Server? Expand / Collapse
Author
Message
Posted Sunday, August 24, 2003 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 4, 2010 10:19 PM
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.




Post #15591
Posted Sunday, August 24, 2003 10:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:27 PM
Points: 33,204, Visits: 15,351
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
Post #76332
Posted Sunday, August 24, 2003 11:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 4, 2010 10:19 PM
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



Post #76333
Posted Monday, August 25, 2003 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:27 PM
Points: 33,204, Visits: 15,351
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
Post #76334
Posted Monday, August 25, 2003 7:20 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
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"
Post #76335
Posted Tuesday, August 26, 2003 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 4, 2010 10:19 PM
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.







Post #76336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse