Selecting latest record with info

  • I have a problem that I've been using what I call a "clumsy" workaround for so far, but would like to know if someone else has an obvious answer.

    I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:

    -- create table

    if object_id('tempdb..#tmpdelnotes') is not null

    drop table #tmpdelnotes

    create table #tmpdelnotes(

    DelNote int identity (1,1) ,

    DelDate date not null,

    Item int not null,

    Customer int not null)

    -- populate table

    insert into #tmpdelnotes values

    ('20140821',10,1),

    ('20140829',10,1),

    ('20140730',20,1),

    ('20131231',30,2),

    ('20140601',10,3)

    -- select ordered data (full set for visibility)

    select customer, item, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn

    from #tmpdelnotes

    -- "clumsy" workaround to get a table with unique "Item"

    select t1.customer, t1.item, t1.delnote, t1.deldate

    from (

    select item, customer, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn

    from #tmpdelnotes

    ) t1

    where t1.rn=1

    What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.

  • There are a few alternatives, such as this:

    SELECT MyStuff

    FROM TableList t

    OUTER APPLY (

    SELECT TOP 1 item, customer, delnote, deldate

    FROM #tmpdelnotes d

    WHERE d.Item = t.Item

    ORDER BY deldate DESC

    ) ou

    Can you provide an example query?

    โ€œ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

  • I don't think your approach is clumsy, in fact if you add a POC (Partition Order Cover) index on the table, this method is very hard to beat!

    ๐Ÿ˜Ž

    -- create table

    if object_id('tempdb..#tmpdelnotes') is not null

    drop table #tmpdelnotes

    create table #tmpdelnotes(

    DelNote int identity (1,1) ,

    DelDate date not null,

    Item int not null,

    Customer int not null)

    -- populate table

    insert into #tmpdelnotes values

    ('20140821',10,1),

    ('20140829',10,1),

    ('20140730',20,1),

    ('20131231',30,2),

    ('20140601',10,3);

    CREATE NONCLUSTERED INDEX TEMP_DEL_NOTES_POC_LAST_VALUE ON #tmpdelnotes (item ASC, deldate DESC) INCLUDE (DelNote,Customer)

    -- select ordered data (full set for visibility)

    select customer, item, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn

    from #tmpdelnotes

    -- "clumsy" workaround to get a table with unique "Item"

    select t1.customer, t1.item, t1.delnote, t1.deldate

    from (

    select item, customer, delnote, deldate, row_number() over (partition by item order by deldate desc) as rn

    from #tmpdelnotes

    ) t1

    where t1.rn=1

Viewing 3 posts - 1 through 2 (of 2 total)

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