equivalent for rownum

Question

Post reply

equivalent for rownum

newbieuser

SSCrazy Eights

Points: 9423
More actions
September 8, 2009 at 1:05 pm

#209753

Hi friends,
I do 'select rownum from table' in oracle to select rows ordered by rownum. What is the equivalent in sql server?
Thanks

Viewing 15 posts - 1 through 15 (of 32 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

That functionality isn't really built in to SQL server but you can do this if you are using SQL 2005 or later

SELECT *, ROW_NUMBER() OVER (ORDER BY [column name]) as rowNum

FROM

order by ROW_NUMBER() OVER (ORDER BY [column name])

You would have to change the [column name] and

.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

newbieuser (9/8/2009)
I do 'select rownum from table' in oracle to select rows ordered by rownum.

:w00t: No, you don't.

In the Oracle world rownum is a pseudo-column, Oracle assigns -to rownum- a value from 1 up to all rows in a retrieved dataset. Not matter the order rownum will always look like 1, 2, 3, ... , etc.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 3

Just as a matter of technical interest:

If ROWNUM in Oracle returns rows ordered by the internal row identifier (RID) of the table, then SQL Server does have something similar, though it is undocumented, likely to change any moment, and will cause your shoes to catch fire if you use it for anything other than curiosity value:

*** 2008 only - see my next post for a 2005 version ***

CREATE TABLE dbo.A (id INT NOT NULL);

INSERT dbo.A VALUES (1)

INSERT dbo.A VALUES (2)

INSERT dbo.A VALUES (3)

SELECT id, %%PhysLoc%% from dbo.A ORDER BY %%PhysLoc%%;

The value returned can be converted to a true heap RID with some magic.

Never use this. Did I mention that already?

Paul

edit: added the ORDER BY to better match the original question, and to add Lowell's observation 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lowell SSC Guru Points: 323518 More actions · Answer 4

Paul i tried your example on SQL2005 Standard, SP3, but i get a syntax error in a 9.0 compatibility database, as well as master. is that a 2008 feature? it is really percent-percent-PhysLoc-percent-percent, right? no paste error?

Msg 102, Level 15, State 3, Line 7

Incorrect syntax near 'PhysLoc'.

productversion productlevel edition

9.00.4035.00 SP3 Standard Edition

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 5

Hey Lowell,

Thanks for that! Yes it is 2008-specific, but the following will work in both (though not quite the same thing - it's close)

CREATE TABLE dbo.A (id INT NOT NULL);

INSERT dbo.A VALUES (1)

INSERT dbo.A VALUES (2)

INSERT dbo.A VALUES (3)

SELECT id, %%LockRes%% from dbo.A ORDER BY %%LockRes%%;

DROP TABLE dbo.A

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lowell SSC Guru Points: 323518 More actions · Answer 6

that is one of the coolest things I've seen in a long time; Thanks Paul, I learned something,and have a shiny new toy to play with now.

I added this to my snippets.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 7

You're welcome - but make sure you fire-proof your shoes eh?

BTW you may notice that %%LockRes%% output bears a resemblance to entries in the resource_description column of sys.dm_tran_locks for RID and KEY lock types. You may also see it in places like Activity Monitor...

Have fun 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Paul White (9/8/2009)
If ROWNUM in Oracle returns rows ordered by the internal row identifier

...actually, it doesn't.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 9

PaulB (9/8/2009)
Paul White (9/8/2009)
If ROWNUM in Oracle returns rows ordered by the internal row identifier
...actually, it doesn't.

Not that I really care, but it turns out I was thinking of Orcale's ROWID.

ROWNUM seems to be a sort of dynamic IDENTITY(1,1) or ROW_NUMBER() OVER (ORDER BY ).

Thanks for posting such a super-helpful and detailed reply though - well done sir.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

Paul White (9/8/2009)

Thanks for posting such a super-helpful and detailed reply though - well done sir.

Just to make it clear, rownum does not exist in real world, it's a pseudo-column. Think of it as the sequence number of rows returned by a query no matter how rows are ordered.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 11

PaulB (9/8/2009)
Just to make it clear, rownum does not exist in real world, it's a pseudo-column. Think of it as the sequence number of rows returned by a query no matter how rows are ordered.

Thanks.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

So, with that in mind, the following seems to emulate the ROWNUM pseudo-column:

-- Assumes AdventureWorks sample database is available

WITH ResultSet AS

(

SELECT TOP (9223372036854775807) *

FROM [AdventureWorks].[HumanResources].[Employee]

ORDER BY

[BirthDate] ASC

)

SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

*

FROM ResultSet;

GO

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

PaulB (9/8/2009)
newbieuser (9/8/2009)
I do 'select rownum from table' in oracle to select rows ordered by rownum.
:w00t: No, you don't.
In the Oracle world rownum is a pseudo-column, Oracle assigns -to rownum- a value from 1 up to all rows in a retrieved dataset. Not matter the order rownum will always look like 1, 2, 3, ... , etc.

Maybe not like that, but I've used it in relation to derived tables and it does work as advertised. Of course, it doesn't help that the OP excluded the highly recommended ORDER BY that should go with it.

--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)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 14

Jeff Moden (9/8/2009)Maybe not like that, but I've used it in relation to derived tables

You are correct. Smart people are able to do all kinds of magic like using rownum to "remember" the order in which rows got delivered by a previous query.

In this specific case OP was relying on rownum to get "ordered" rows from a table which would never happen.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.