Post reply

Select last non null values from a table

  • September 10, 2009 at 8:16 am

    #138700

    I have a table like this:

    col1 col2 col3 col4

    11 22 34 2009-09-01

    NULL 53 32 2009-09-02

    NULL 26 NULL 2009-09-04

    I want to get the last non null values for each column. So the result should be:

    col1 col2 col3 col4

    11 26 32 2009-09-04

    Thanks

  • September 10, 2009 at 9:24 am

    #1051791

    SELECT MAX(col1) col1,MAX(col2) col2,MAX(col3) col3,MAX(col4) col4

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • September 10, 2009 at 12:19 pm

    #1051870

    This solution doesn't work because values from columns are not ordered except the column that store the timestamp.

  • September 10, 2009 at 12:53 pm

    #1051897

    ioan.ivanov (9/10/2009)

    This solution doesn't work because values from columns are not ordered except the column that store the timestamp.

    I knew reading these too quickly was gonna bite me eventually. If Col4 was your timestamp column:

    SELECT

    (SELECT TOP 1 COL1 FROM YourTable WHERE COL1 IS NOT NULL Order by Col4 DESC) COL1,

    (SELECT TOP 1 COL2 FROM YourTable WHERE COL2 IS NOT NULL Order by Col4 DESC) COL2,

    (SELECT TOP 1 COL3 FROM YourTable WHERE COL3 IS NOT NULL Order by Col4 DESC) COL3

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

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