Find the latest value of each column based on row_number rather than max or min!!?!

  • I need the latest value of each column, where 'latest' is defined as the most recent not null value, and 'most recent' being defined as the sequence in which the order by on the row_number command is returned, which in the example below is based on date. Difficult to say it in English, maybe the best way I can describe this is with an example:

    declare @a table(cust int, b int, c int, d int, e int, f datetime)

    insert @a select 1,null,null, 1, 1, '1 jan 2010'

    insert @a select 1, 2,null, 1, 1, '2 jan 2010'

    insert @a select 1, 4, 3, 1, 1, '3 jan 2010'

    insert @a select 1, 5, 41, 4, 1, '5 jan 2010'

    insert @a select 1, 6, 2, 1, 5, '4 jan 2010'

    insert @a select 1, 7,null, 1, 1, '6 jan 2010'

    insert @a select 2, 8, 1, 71, 1, '7 jan 2010'

    insert @a select 2, 9, 1,null, 1, '8 jan 2010'

    insert @a select 2,null, 11,null, 31, '9 jan 2010'

    select cust, b, c, d, e, ROW_NUMBER() over (partition by cust order by f desc) z, f

    from @a

    This gives this result set:

    cust b c d e z

    ----------- ----------- ----------- ----------- ----------- --------------------

    1 7 NULL 1 1 1

    1 5 41 4 1 2

    1 6 2 1 5 3

    1 4 3 1 1 4

    1 2 NULL 1 1 5

    1 NULL NULL 1 1 6

    2 NULL 11 NULL 31 1

    2 9 1 NULL 1 2

    2 8 1 71 1 3

    The result set I want for cust,b,c,d,e is........

    1,7,41,1,1

    2,9,11,71,31

    Sure this can be done with cursors, or a while loop updating where not null, etc, but it's very unwieldy and I'm sure there has to be a better way!?

  • An alternative is going to be a #tmp with specific indexes using the Quirky Update method, inverting the row numbering, using quirky to carry values down the nulls, then inverting the select to only pull the last entry for each cust.

    Middle of a different test harness, get back to you on that if that's not enough to go end to end with it. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Is there a limit to the number of entries for a customer?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • No limit.

    I've resorted to a while loop, to hit each row with an update, but would still love a better approach as I'm using this in a few places (pardon the sloppy layout below)

    Note : pk column added to table, called pk, numberd 1,2,3 etc, and 'done' bit, defaulted to 0

    declare @pk int

    while exists(select 1 from @a where done = 0)

    begin

    select top 1 @pk = pk from @a where done = 0

    update a set b = isnull(a.b,a2.b), c = ISNULL(a.c,a2.c), d = ISNULL(a.d,a2.d)

    from @a a join @a a2 on a.cust = a2.cust and a.z + 1 = a2.z

    update @a set done = 1 where pk = @pk

    end

    There must be a better way!

  • Try this:

    Be aware, this uses a serial updating methodology and I don't always follow every rule from Jeff's Quirky Update post here on SSC. This will work, however, as long as you completely control the #tmp. This should be wrapped (during the update) with the safety cte found here for complete assurance: http://www.sqlservercentral.com/Forums/Topic802558-203-4.aspx#bm980118

    DROP TABLE #tmp

    GO

    declare @a table(cust int, b int, c int, d int, e int, f datetime)

    insert @a select 1,null,null, 1, 1, '1 jan 2010'

    insert @a select 1, 2,null, 1, 1, '2 jan 2010'

    insert @a select 1, 4, 3, 1, 1, '3 jan 2010'

    insert @a select 1, 5, 41, 4, 1, '5 jan 2010'

    insert @a select 1, 6, 2, 1, 5, '4 jan 2010'

    insert @a select 1, 7,null, 1, 1, '6 jan 2010'

    insert @a select 2, 8, 1, 71, 1, '7 jan 2010'

    insert @a select 2, 9, 1,null, 1, '8 jan 2010'

    insert @a select 2,null, 11,null, 31, '9 jan 2010'

    select cust, b, c, d, e, ROW_NUMBER() over (partition by cust order by f desc) z, f

    from @a

    CREATE TABLE #tmp

    (cust INT,

    b INT,

    c INT,

    d INT,

    e INT,

    f DATETIME,

    OrderingInt INT)

    INSERT INTO #tmp

    SELECT

    Cust, b, c, d, e, f,

    ROW_NUMBER() over ( partition by cust order by f ASC)

    FROM

    @a

    CREATE CLUSTERED INDEX idx_#tmp ON #tmp (Cust, OrderingInt)

    SELECT * FROM #tmp

    DECLARE @cust INT, @b-2 INT, @C INT, @d INT, @e INT, @f INT

    UPDATE#tmp

    SET@b-2 = b = CASE WHEN @Cust = CUST THEN ISNULL( b, @b-2) ELSE b END,

    @C = c = CASE WHEN @Cust = CUST THEN ISNULL( c, @C) ELSE c END,

    @d = d = CASE WHEN @Cust = CUST THEN ISNULL( d, @d) ELSE d END,

    @e = e = CASE WHEN @Cust = CUST THEN ISNULL( e, @e) ELSE e END,

    @cust = cust

    OPTION ( MAXDOP 1)

    SELECT * from #tmp

    SELECT

    t.cust,

    t.b,

    t.c,

    t.d,

    t.e,

    t.f

    FROM

    #tmp AS t

    JOIN

    (SELECT

    cust, MAX(OrderingInt) AS MaxOI

    FROM

    #tmp AS t2

    GROUP BY

    cust

    ) AS drv

    ONt.cust = drv.cust

    AND t.OrderingInt = drv.MaxOI

    EDIT: There was an error in the index declaration. Please re-review that statement.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 5 posts - 1 through 4 (of 4 total)

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