måndag 28 oktober 2013

select max from multiple columns

I have a view reading from three tables: A, B and C. Each table has a Created timestamp. I want the view to show the latest timestamp in any of these three tables, to be able to find new rows since a certain date, even if it is just a new row in one of the tables.

I was about to do a case or a function, but found this little simple method.

This snippet returns 3 with column name of "value"
SELECT Max(v)
FROM ( VALUES (1), (2), (3) ) AS value(v)

Select Col1, Col2,
(
SELECT Max(v)
FROM ( VALUES (a.Created), (b.Created), (c.Created) ) AS value(v)
) as newDataCreated
From A
inner join B
on a.id = b.a_id
inner join C
on b.id = c.b_id

Works in SQL Server 2008 and newer versions.