Assign Row number to the result set without using Row_Number

Question

Assign Row number to the result set without using Row_Number

Viewing 10 posts - 16 through 24 (of 24 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!

And no reasons why...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Michael Valentine Jones

SSC Guru

Points: 64818

August 15, 2014 at 7:55 am

#1738406

Is this a homework question?

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

Can you provide more reasons why you need a row number? There might be a solution that doesn't involve row number directly.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!

Can one use pixy dust?

😎

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

Since the OP provided no reason why not to use ROW_NUMBER() or temp tables (in spite of being asked again and again), I can't see why we should continue to brainstorm elaborate work-arounds.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Eirikur Eiriksson (8/15/2014)
Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!
Can one use pixy dust?

Only if it's ANSI Standard dust.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 7

GilaMonster (8/15/2014)
Eirikur Eiriksson (8/15/2014)
Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!
Can one use pixy dust?
Only if it's ANSI Standard dust.

BWAAA-HAAA!!!! Oh my poor internal eyes! I just got this real live vision of Joe Celko in a tu-tu under his black vest flying around with little wings with ANSI stamped on one and ISO stamped on the other with a magic wand that looked like a rolled up Hollerith card. Gonna take a lot of beer tonight to get rid of that image!

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Jeff Moden (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/15/2014)
Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!
Can one use pixy dust?
Only if it's ANSI Standard dust.
BWAAA-HAAA!!!! Oh my poor internal eyes! I just got this real live vision of Joe Celko in a tu-tu under his black vest flying around with little wings with ANSI stamped on one and ISO stamped on the other with a magic wand that looked like a rolled up Hollerith card. Gonna take a lot of beer tonight to get rid of that image!

Thank you, Jeff. Now we've all had the image of Joe in a tu-tu. :sick: I think it's time to go home. Can DBCC TIMEWARP being applied to do a selective truncate against my memory only to a point-in-time? Or where's that Clorox that Sean posted?

I personally can't wait to hear the reasoning against ROW_NUMBER() or the aversion to temp tables.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 9

Ed Wagner (8/15/2014)
Jeff Moden (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/15/2014)
Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.
And no loops!
Can one use pixy dust?
Only if it's ANSI Standard dust.
BWAAA-HAAA!!!! Oh my poor internal eyes! I just got this real live vision of Joe Celko in a tu-tu under his black vest flying around with little wings with ANSI stamped on one and ISO stamped on the other with a magic wand that looked like a rolled up Hollerith card. Gonna take a lot of beer tonight to get rid of that image!
Thank you, Jeff. Now we've all had the image of Joe in a tu-tu. :sick: I think it's time to go home. Can DBCC TIMEWARP being applied to do a selective truncate against my memory only to a point-in-time? Or where's that Clorox that Sean posted?
I personally can't wait to hear the reasoning against ROW_NUMBER() or the aversion to temp tables.

That should teach you Ed, always checkpoint before reading the posts:-P Jeff, you are going to need something with a lot more umpf than a beer to clear this one out, cannot help thinking of how you are going to react next time you meet him in person:-D

😎

BTW Jeff, "thanks" for sharing.......\0/...........

Priya004 Ten Centuries Points: 1368 More actions · Answer 10

Yes ....Plz close this discussion..

Im okkkkkkkkkk to use row number as there doesn't seems to be better option..

Thanks to all