How to get column name for max date in a very large table

Question

I have a table that has 38 columns that are all datetime data type. I want to query the column name that has the max date in each row.

I have figured out how to determine the max date of each row, but I'm not sure how to query which column it came from.

IF OBJECT_ID('tempdb..#MaxDates') IS NOT NULL DROP TABLE #MaxDates SELECT [ColumnOne], Max( StartDate) as MaxEffDate Into #MaxDates FROM dbo.DatabaseName group by [ColumnOne] Select a.[ColumnOne], StartDate, (SELECT MAX(LastUpdateDate) FROM (VALUES (case when (ColumnTwo) < '9999-12-31' then (ColumnTwo) else '1900-01-01' end) ) AS UpdateDate(LastUpdateDate) ) AS LastUpdateDate From dbo.DatabaseName a inner join #MaxDates on a.[ColumnOne] =#MaxDates.[ColumnOne] and a.StartDate = #MaxDates.MaxEffDate order by a.[ColumnOne] asc

I expect the following result-

ColumnOne, StartDate, LastUpdateDate, LastUpdateDateColumnName

It's not really clear to me how the text of the question relates to it's code and the desired output. Please read the first paragraph of the Sql-Server tag info and edit your question accordingly. — Zohar Peled
– Zohar Peled, Commented Jul 8, 2019 at 13:14

Squirrel · Accepted Answer · 2019-07-08 13:19:50Z

you almost got it with your current query. You can make use of the VALUE or even CROSS APPLY to find the column with the max date

select t.[ColumnOne], t.StartDate, d.LastUpdateDate, d.LastUpdateDateColumnName from dbo.DatabaseName t cross apply ( select top 1 LastUpdateDate, LastUpdateDateColumnName from ( values (Date1, 'Date1'), (Date2, 'Date2'), (Date3, 'Date3') .... ) as d (LastUpdateDate, LastUpdateDateColumnName) order by LastUpdateDate desc ) d

Collectives™ on Stack Overflow

How to get column name for max date in a very large table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related