Linking to the Previous Row

Question

Linking to the Previous Row

David McKinney

SSChampion

Points: 10472
More actions
March 12, 2008 at 11:00 pm

#179987

Comments posted to this topic are about the item Linking to the Previous Row

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

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

jordonpilling SSCommitted Points: 1690 More actions · Answer 1

a very nice article indeed, however i recently had the same issue on SQL Server 8.0, Is their a related atricle/method for SQL 8?

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

David McKinney SSChampion Points: 10472 More actions · Answer 2

Hi Jordon,

As you probably realise, SQL 2000 doesn't have CTEs or RowNumber.

I was happy to discover CTEs precisely because I'm hopeless at doing queries like the one below!! So here's something to get you started...

...but I'm sure someone will chip in with a complete / better solution.

select currow.ItemId,

( select max(PriceStartDate)

from PriceHistory phPrev

where phPrev.PriceStartDate < currow.PriceStartDate

and phPrev.ItemId=currow.ItemId

) as OldPrice,

currow.PriceStartDate,

currow.Price

from PriceHistory currow

Change the smiley for a closing bracket.

Hope this helps....

David.

jordonpilling SSCommitted Points: 1690 More actions · Answer 3

hmmm, yes based on that i have come up with a simple sub-select approach:

select * from SELECT DailyMileageID, MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, MileageAtEndOfShift -

(SELECT MileageAtStartOfShift

FROM dbo.DailyMileages AS I

WHERE (DailyMileageID = b.DailyMileageID + 1)) AS NightMileage, ShopID, DateEntered

FROM dbo.DailyMileages AS b

which works, but i am a little bothered about performance, but yes, thanks for the tip :

next task... convince this company to upgrade their sql server 😀

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

David McKinney SSChampion Points: 10472 More actions · Answer 4

Be careful about using DailyMileageID+1. You could have holes if it's an identity column (when you delete rows, for example), and also is the sort order definitely correct?

Look in your data for another way of identifying the next / previous row.

Regards,

David.

Michael Lato Ten Centuries Points: 1278 More actions · Answer 5

As you mention in the article I maintain audit trails by storing both a start date and an end date in my history tables. How is the overall performance of the CTE method for extracting a point-in-time view of your data? Is it fast enough to handle reporting on demand?

Regards,
Michael Lato

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Nice article. I like the fact that is clearly takes you from start to finish and offers a solution to a commonly encountered problem.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Bill Coale-478581 SSC Enthusiast Points: 154 More actions · Answer 7

For this date range technique, for the end date:

nextrow.PriceStartDate AS EndDate

I usually do:

dateadd(day, -1, nextrow.PriceStartDate) AS EndDate

Also truncate to midnight the start/end date and any date comparisons to the range

David McKinney SSChampion Points: 10472 More actions · Answer 8

How is the overall performance of the CTE method for extracting a point-in-time view of your data? Is it fast enough to handle reporting on demand?

I haven't done any specific volume testing on this. All I can say is that I'm using it in production systems, without any noticeable performance hit. Obviously storing the data inline is going to be faster, but to what degree I honestly don't know.

I'm one of those developers who will often choose the elegant solution over the fastest solution, providing it is fast enough for the application being developed.

Regards,

David.

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

Jack Corbett (3/13/2008)
Nice article. I like the fact that is clearly takes you from start to finish and offers a solution to a commonly encountered problem.

Agreed... and test data used was attached in the Resources area... nicely done.

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 10

jordonpilling (3/13/2008)
a very nice article indeed, however i recently had the same issue on SQL Server 8.0, Is their a related atricle/method for SQL 8?

You should be able to do the same thing by making the PriceCompare a temporary table with an identity column instead of the ROW_NUMBER() for rownum.

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

Seth-284448 SSC-Addicted Points: 458 More actions · Answer 11

I've had to link on a previous row in the past. I've just used self joins, i.e. joining a table on itself. In the join clause, I just put my criteria. For example:

select t1.price as 'oldprice', t2.price as 'newprice' from table1 t1 join table1 t2 on t1.colnum +1= t2.colnum

Is there anything wrong with doing that? Is it less efficient or buggy?

Thanks,

Seth

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

In your example you are assuming that the PK field is incrementing by 1 and when you have data like in the example that cannot be guaranteed. The CTE version in the article is simple, easy to read and understand, and adaptable. I have used similar methods to your self join, but they really involve joining to derived tables. The CTE is cleaner and easier to read and, I think, should be the preferred way to handle it in SQL 05 and later.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 13

Seth (3/13/2008)
I've had to link on a previous row in the past. I've just used self joins, i.e. joining a table on itself. In the join clause, I just put my criteria. For example:
select t1.price as 'oldprice', t2.price as 'newprice' from table1 t1 join table1 t2 on t1.colnum +1= t2.colnum
Is there anything wrong with doing that? Is it less efficient or buggy?
Thanks,
Seth

That method works just fine so long as you are linking to the immediately prior ID. If, for example, you want the prior order for the same customer, your ID number is not so easily derived.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tom Garth SSCertifiable Points: 6193 More actions · Answer 14

CREATE TABLE #PriceCompare (

ITEMNMBR varchar(31),

ITEMDESC varchar(101),

DOCDATE datetime,

UNITCOST numeric(19, 5),

rownum int identity

)

INSERT INTO #PriceCompare

SELECT

RTRIM(PH.ITEMNMBR) [ITEMNMBR],

RTRIM(i.ITEMDESC) [ITEMDESC],

ph.DOCDATE,

ph.UNITCOST

FROM IV00101 i

INNER JOIN IV30300 ph

ON i.ITEMNMBR = ph.ITEMNMBR

ORDER BY i.ITEMNMBR, ph.DOCDATE

SELECT

currow.ITEMDESC,

ISNULL(prevrow.UNITCOST, 0.00) AS OldCost,

currow.UNITCOST AS RangeCost,

CONVERT(varchar(10), currow.DOCDATE, 110) AS StartDate,

ISNULL(CONVERT(varchar(10), nextrow.DOCDATE, 110), 'CURRENT') AS EndDate

FROM #PriceCompare currow

LEFT JOIN #PriceCompare nextrow

ON currow.rownum = nextrow.rownum - 1

AND currow.ITEMNMBR = nextrow.ITEMNMBR

LEFT JOIN #PriceCompare prevrow

ON currow.rownum = prevrow.rownum + 1

AND currow.ITEMNMBR = prevrow.ITEMNMBR

DROP TABLE #PriceCompare

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