SQL Max Date of multiple columns, return column name

Question

Using MS SQL Server 2008, I'm doing a query to calculate the Max date of 10 Columns for each Row. That query is below. I also need to list the name of that column that has the Max date. So the Query would give me the [Last Step Date] and the [Last Step Name]. How do I get the [Last Step Name]?

SELECT (SELECT MAX(LastUpdateDate) FROM (VALUES ([Step 1]), ([Step 2]), ([Step 3]), ([Step 4]), ([Step 5]), ([Step 6]), ([Step 7]), ([Step 8]), ([Step 9]), ([Step 10]) ) AS UpdateDate(LastUpdateDate)) AS [Last Step Date] FROM MyTable

Martin Smith · Accepted Answer · 2017-12-29 19:07:48Z

You can use

SELECT ca.ColName, ca.LastUpdateDate FROM YourTable CROSS APPLY (SELECT TOP 1 * FROM (VALUES ('Step1', [Step 1]), ('Step2', [Step 2]), ('Step3', [Step 3]), ('Step4', [Step 4]), ('Step5', [Step 5]), ('Step6', [Step 6]), ('Step7', [Step 7]), ('Step8', [Step 8]), ('Step9', [Step 9]), ('Step10', [Step 10]) ) UpdateDate(ColName, LastUpdateDate) ORDER BY LastUpdateDate DESC)ca

SQL Fiddle

Thanks, that definitely works! I still need to work something out as when I inserted it into the entire query that is multiple temp tables coming together, it's pulling the same value for all rows, but I should be able to figure that out.

Collectives™ on Stack Overflow

SQL Max Date of multiple columns, return column name

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related