info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Get Maximum Value from a list of Columns Values
by BF (Principal Consultant; Architecture; Engineering)
2017-01-12








T-SQL Example:


Step 1: Create a table:

CREATE TABLE #TMP (Date1 VARCHAR(10), Date2 VARCHAR(10), Date3 VARCHAR(10), Date4 VARCHAR(10), Date5 VARCHAR(10))


Step 2: Insert some data:

INSERT INTO #TMP ( Date1, Date2, Date3, Date4 , Date5)
VALUES ('20170101', '20170201', '20170301', '20170401', '20170501')


Get all Columns plus a Max Value:

SELECT Date1, Date2, Date3, Date4, Date5, (SELECT MAX(n) FROM (VALUES (Date1), (Date2), (Date3), (Date4), (Date5)) AS t(n)) AS MaxDate FROM #TMP


Get MaxValue:

SELECT (SELECT MAX(n) FROM (VALUES (Date1), (Date2), (Date3), (Date4), (Date5)) AS t(n)) AS MaxDate FROM #TMP


Get all Columns plus a Min Value:

SELECT Date1, Date2, Date3, Date4, Date5, (SELECT MIN(n) FROM (VALUES (Date1), (Date2), (Date3), (Date4), (Date5)) AS t(n)) AS MinDate FROM #TMP


Get Min Value:

SELECT (SELECT MIN(n) FROM (VALUES (Date1), (Date2), (Date3), (Date4), (Date5)) AS t(n)) AS MinDate FROM #TMP