January 26, 2012 at 12:44 pm
SQLRNNR (1/26/2012)
ChrisM@home (1/26/2012)
SQLRNNR (1/26/2012)
ChrisM@home (1/26/2012)
SQL Kiwi (1/26/2012)
...Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.It's an obvious use for rCTE's really. I wanted to see if rCTEs can outperform the dual window method - and the results of testing in that thread show that they can, and suggest when an rCTE might be best choice. As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
I started down the rCTE path last night and then rechecked this thread. I saw the dense_rank solution and abandoned my rCTE solution. Kudos on that solution Chris.
Cheers Jason 🙂
You showed me another thing I have to learn.:hehe:
Gosh that sounds a bit ominous :ermm:
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 26, 2012 at 2:48 pm
ChrisM@home (1/26/2012)
As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
It's not just you. And the performance thing is interesting: the rCTE has more predictable performance in most real-world cases, I would wager. One of the problems with sorts (as required for the double-numbering method) is that you can never be sure they will occur in memory. Good performance with an in-memory sort can all too easily turn into a disaster when multiple sort passes to tempdb are required.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply