Linking to the Previous Row

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

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

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

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

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

  • 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

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

  • 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

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

  • 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)
    Intro to Tally Tables and Functions

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

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

  • 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

  • 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

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

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