Linking to the Previous Row

Question

Post reply

Linking to the Previous Row

Viewing 15 posts - 16 through 30 (of 147 total)

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 1

Mike DiRenzo

SSCrazy

Points: 2013

March 13, 2008 at 11:03 am

#789217

Great article. Thanks.

-Mike

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

I recently had this question, and because I'm using SS2K, couldn't use CTEs. Thanks to this terrific forum, I was provided with a very nice solution that doesn't rely on CTEs or row numbers. Check out this link

http://www.sqlservercentral.com/Forums/Topic462350-373-1.aspx#bm462366

Mattie

knechod SSC-Addicted Points: 482 More actions · Answer 3

Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?

--
Please upgrade to .sig 2.0

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Nice article...very interesting solution! I like the RowNumber with partition functionality, seems useful for a number of things.

My only complaint is that the SQL Code is very difficult to read in those little itty boxes.

Signature is NULL

Manie Verster SSCertifiable Points: 7152 More actions · Answer 5

Hi David,

:P:P Thanks for a very nice article. I am still a bit new on SQL 2005 so I had a bit of difficulty to understand the code. I come from a MSSql 7 background and must say I have learned a lot from this forum. I immediately tested your code and after battling just a bit I got it right. In the past I solely used views for the purpose of quickly listing some data but this way is very nice.

Thanks a span.

Confusius said: "Ask a question and be a foll for a moment, keep quiet and remain a fool forever"

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

David McKinney SSChampion Points: 10472 More actions · Answer 6

knechod (3/13/2008)
Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?

Hi,

Hmmm.... You're not wrong! You could remove the partition clause, but you'd have to put ItemId in the OrderBy. Otherwise there will not be an uninterrupted (contiguous) sequence for a specific Item.

so if you prefer

OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum

could become

OVER (ORDER BY ph.ItemId, PriceStartDate) AS rownum

I just find conceptually it's easier to understand when the numbering restarts. But it would be interesting to see if the performance improved with your method.

David McKinney SSChampion Points: 10472 More actions · Answer 7

Just a quick note to say thanks to everyone for all their feedback. My previous articles were about xml and xsl and while apparently appreciated, they didn't generate any real discussion.

So I'm delighted to hear what you made of this one.

Thanks again,

David McKinney.

p.s. I think there may be a question mark over the performance for very large datasets, so it's probably not suitable for every scenario, but should work fine for most of us.

Lisa Slater Nicholls Hall of Fame Points: 3453 More actions · Answer 8

There are a number of ways to "fake" the rownumber function in sql 2000 -- here is one I particularly like:

http://support.microsoft.com/kb/q186133/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you should really read the following article...

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

knechod SSC-Addicted Points: 482 More actions · Answer 10

David McKinney (3/14/2008)
knechod (3/13/2008)
Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?
Hi,
Hmmm.... You're not wrong! You could remove the partition clause, but you'd have to put ItemId in the OrderBy. Otherwise there will not be an uninterrupted (contiguous) sequence for a specific Item.
so if you prefer
OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
could become
OVER (ORDER BY ph.ItemId, PriceStartDate) AS rownum
I just find conceptually it's easier to understand when the numbering restarts. But it would be interesting to see if the performance improved with your method.

Actually, I'm getting MUCH too much credit on this. After taking a look at the BOL (heartily recommended before posting!), I see that ROW_NUMBER requires the OVER clause. I apologize for letting my 'Oracle-ness' spill into this discussion! Now, I understand why Partition may or may not be necessary!

--
Please upgrade to .sig 2.0

Lisa Slater Nicholls Hall of Fame Points: 3453 More actions · Answer 11

>>

Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you should really read the following article...

<<

Thanks Jeff. I do realize that this is a form of RBAR.

FWIW I have not had to use these because I haven't ever done it in SQL 2000 myself. (I didn't use SQL 2000 very much at all, actually.)

However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.

If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.

>L<

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

I hope to show in this article that the CTEs usefulness is not purely cosmetic.

You are right david. It would be immensely useful in more complicated situations. I would have to do as follows without the use of CTE in the present example.

CREATE VIEW [dbo].[PriceCompare] AS

SELECT

currow.Item,

prevrow.Price AS OldPrice,

currow.Price AS RangePrice,

currow.PriceStartDate AS StartDate,

nextrow.PriceStartDate AS EndDate

FROM

(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,

ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum

FROM Items i INNER JOIN PriceHistory ph

ON i.ItemId = ph.ItemId) currow

LEFT JOIN

(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,

ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum

FROM Items i INNER JOIN PriceHistory ph

ON i.ItemId = ph.ItemId) nextrow

ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId

LEFT JOIN

(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,

ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum

FROM Items i INNER JOIN PriceHistory ph

ON i.ItemId = ph.ItemId) prevrow

ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId

Too clumsy and reiterative as you see. I am looking forward to your input for 'using CTEs with recursive queries'

Best regards,

Maz

-----------------------------------------------------------[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]

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

Lisa Slater Nicholls (3/16/2008)
>>
Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you should really read the following article...
<<
Thanks Jeff. I do realize that this is a form of RBAR.
FWIW I have not had to use these because I haven't ever done it in SQL 2000 myself. (I didn't use SQL 2000 very much at all, actually.)
However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.
If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.
>L<

Thank you very much for the feedback, Lisa. I usually do the same because SQL Server 2000 is still so very prevalent...

As for a suggestion on how to do a "rownumber" in SQL Server 2000, as odd as it seems, a SELECT INTO on a temp table with an identity column is so very fast that it's nearly as fast as SQL Server 2005's ROW_NUMBER() over. Yes, the ROW_NUMBER function is still faster, but the SELECT INTO method is, I believe, the fastest method you can achieve in SQL Server 2000 even when a million rows are involved.

And, I didn't mean to criticise... only to advise. I apologize if the former seemed to be the case.

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

Lisa Slater Nicholls Hall of Fame Points: 3453 More actions · Answer 14

>>

SELECT INTO on a temp table with an identity column is so very fast that it's nearly as fast as SQL Server 2005's ROW_NUMBER() over

<<

That's extremely cool, Jeff -- not ever having tested I would not have thought it would be fast, but from now on I will remember to suggest that, thank you.

I wonder if that is how it's implemented, under the covers, in SQL Server 2005.

>L<

Tom Garth SSCertifiable Points: 6193 More actions · Answer 15

Lisa Slater Nicholls (3/16/2008)
>>
However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.
If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.
>L<

Lisa,

The code I posted here a few days ago is SQL Server 2000 compatible. It is the same as Jeff's SELECT INTO, except that I have explicity defined the table structure. It returns the same data as the original CTE example this thread is based on.

http://www.sqlservercentral.com/Forums/FindPost468899.aspx

The field names were changed to work with a database that I have handy.

I've used this temp table-identity column many times in the past for various reasons, and haven't found it to cause any performance issues.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers