equivalent for rownum

Question

equivalent for rownum

Viewing 3 posts - 31 through 32 (of 32 total)

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

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

Mazharuddin Ehsan (9/11/2009)
Let us say I have a flat file containg rows of data and I want to uplaod the file to a database table with an additional column comprising of the line number as it is appearing in the text file.

:blink: ok, lets go back to the basics.

#1... Poster wanted to know how to replicate Oracle's "rownum" functionality in SQL Server - lets forget for a minute "rownum" doesn't work the way Poster thinks it work.

#2... Since "rownum" is nothing but a pseudo-column populated at the time rows are made available to the user as a result of a "select" statement... don't you think it's a little over reaching the load-a-text-file scenario? 😀

I feel like this is one of those cases where people try to justify anything resorting to no-matter-how-weird-and-far-away-from-reality justification - like changing the original post just to make fit an answer 😉

_____________________________________
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.

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 2

PaulB (9/13/2009)
Mazharuddin Ehsan (9/11/2009)
Let us say I have a flat file containg rows of data and I want to uplaod the file to a database table with an additional column comprising of the line number as it is appearing in the text file.
:blink: ok, lets go back to the basics.
#1... Poster wanted to know how to replicate Oracle's "rownum" functionality in SQL Server - lets forget for a minute "rownum" doesn't work the way Poster thinks it work.
#2... Since "rownum" is nothing but a pseudo-column populated at the time rows are made available to the user as a result of a "select" statement... don't you think it's a little over reaching the load-a-text-file scenario? 😀
I feel like this is one of those cases where people try to justify anything resorting to no-matter-how-weird-and-far-away-from-reality justification - like changing the original post just to make fit an answer 😉

At least now we know what is rownum at last.:-)

More seriously,

1. Actually rownum as it works in Oracle is not required at all in SQL Server as there are many numbering functions available like row_number(), rank() and "TOP" clause. As some one said earlier in this thread 'usage is the key', there is nothing that 'cannot' be solved using these features about dynamically number rows in a SELECT Transact-SQL statement.

In fact one of the practical usage of rownum in Oracle is Top-N Query.

2. If we are really required to create an emulation of rownum 'as it is' in Oracle, for some reason then also it can be done for sure, I beleive in SQl Server. I just gave a quick example. Some people in this thread pointed to some inhibitions in my solution. I can cement it to produce same guarented result as rownum if really required. May be I will not do it for free now 😀

Thanks

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

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

Mazharuddin Ehsan (9/14/2009)Actually rownum as it works in Oracle is not required at all in SQL Server

yes... Yes... YES! you got my vote for President! 😀

Not to mention original poster expected rownum to do in Oracle something totally different at what it actually does. Go figure.

_____________________________________
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.