August 24, 2003 at 10:42 am
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.
August 24, 2003 at 10:12 pm
No, you have to do some convulted SQL to make this work.
Steve Jones
August 24, 2003 at 11:13 pm
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
August 25, 2003 at 9:29 am
Some things work better, some don't. Haven't seen the need for the row number. Why do you need it?
Steve Jones
August 25, 2003 at 7:20 pm
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
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"
August 26, 2003 at 7:59 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply