in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

November 2011 - Posts

  • Finding the maximum value/date across columns

    While working on some code recently I discovered a neat little trick to find the maximum value across several columns…..

    So the starting point was finding the maximum date across several related tables and storing the maximum value against an aggregated record.

    Here's the sample setup code:

    USE TEMPDB
    IF OBJECT_ID('CUSTOMER') IS NOT NULL
    BEGIN
        DROP TABLE CUSTOMER
    END
    
    IF OBJECT_ID('ADDRESS') IS NOT NULL
    BEGIN
        DROP TABLE ADDRESS
    END
    
    
    IF OBJECT_ID('ORDERS') IS NOT NULL
    BEGIN
        DROP TABLE ORDERS
    END
    
    SELECT
        1 AS CUSTOMERID,
        'FREDDY KRUEGER' AS NAME,
        GETDATE() - 10 AS DATEUPDATED
    INTO
        CUSTOMER
    
    SELECT 
        100000 AS ADDRESSID,
        1 AS CUSTOMERID,
        '1428 ELM STREET' AS ADDRESS,
        GETDATE() -5 AS DATEUPDATED
    INTO
        ADDRESS
        
    SELECT
        123456 AS ORDERID,
        1 AS CUSTOMERID,
        GETDATE() + 1 AS DATEUPDATED
    INTO ORDERS
    .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

     

    Now the code used a function to determine the maximum date, this performed poorly. After considering pivoting the data I opted for a case statement, this seemed reasonable until I discovered other areas which needed to determine the maximum date between 5 or more tables which didn't scale well. The final solution involved using the value clause within a sub query as followed.

    SELECT
        C.CUSTOMERID,
        A.ADDRESSID,
        (SELECT
            MAX(DT)
        FROM
            (Values(C.DATEUPDATED),(A.DATEUPDATED),(O.DATEUPDATED)) AS VALUE(DT))
    FROM
        CUSTOMER C
    INNER JOIN
        ADDRESS A ON C.CUSTOMERID = A.CUSTOMERID
    INNER JOIN
        ORDERS O ON O.CUSTOMERID = C.CUSTOMERID
    .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

    As you can see the solution scales well and can take advantage of many of the aggregate functions!

Powered by Community Server (Commercial Edition), by Telligent Systems