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!