Finding the maximum value/date across columns
- by AtulThakor
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!
