A simple example that shows where if you have a set of columns, and you need to quickly find a maximum value among the set. This code will give you a path with a clever use of cross apply to find the maximal value from a set of values that exist on a record.
This creates an arbitrary table (using sysobjects as a surrogate numbers table), and creates time stamps. It then shows how to get a maximum timestamp from those time stamps quickly, and easier to write than cascading case statements.
drop table if exists #tmp_somevalues;
Create table #tmp_somevalues
(
ID int identity(1,1)
, InsertTS datetime not null
, ModifiedTS datetime
, DeletedTS datetime null
, OtherTS datetime
, YourTimeStamp datetime
)
insert into #tmp_somevalues
(
InsertTS
,ModifiedTS
,DeletedTS
,OtherTS
,YourTimeStamp
)
SELECT
InsertTS = getdate() - so.id
,ModifiedTS = getdate() + so.id
,DeletedTS = case when id % 2 = 0 then getdate() + so.id + 1 else null end
,OtherTS = getdate()
,YourTimeStamp = case when id % 3 = 0 then getdate() + so.id - 1 else null end
--select *
FROM sysobjects so
where so.id <= 50
select * from #tmp_somevalues
cross apply
(
SELECT max(tstamps) as MaxTS FROM (
VALUES(InsertTS),
(ModifiedTS),
(DeletedTS),
(OtherTS),
(YourTimeStamp)
)
AS MaxCandidates(tstamps)
) maxval
A simplified version of this code:
SELECT max(id) as MaxID FROM (
VALUES(1),
(2),
(5),
(10),
(11),
(12),
(20)
)
AS MaxCandidates(Id)
