Max value in Case Statements

Question

Max value in Case Statements

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

drew.allen (4/20/2016)
Is there some reason that you chose to use CROSS APPLY rather than CROSS JOIN? I tend to only use CROSS APPLY if the right side is somehow dependent on the left side, which is not the case in this example.
Drew

Nope. I think I meant to type CROSS JOIN and just typed CROSS APPLY instead.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Con Alexis Old Hand Points: 331 More actions · Answer 2

I'd probably use a CROSS APPLY in this instance...

e.g.

WITH Data AS

(

SELECT 1 AS ID, '1/1/1900' AS Date

UNION ALL SELECT 2, '1/1/1900'

UNION ALL SELECT 3, getdate()

UNION ALL SELECT 4, Getdate()

)

SELECT D.*, CASE WHEN D.Date = MD.Date THEN 1 ELSE 0 END AS IsMaxDate

FROM Data D

CROSS APPLY ( SELECT TOP 1 Date FROM Data ORDER BY Date DESC ) MD

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Con Alexis (4/20/2016)
I'd probably use a CROSS APPLY in this instance...
e.g.
WITH Data AS
(
SELECT 1 AS ID, '1/1/1900' AS Date
UNION ALL SELECT 2, '1/1/1900'
UNION ALL SELECT 3, getdate()
UNION ALL SELECT 4, Getdate()
)
SELECT D.*, CASE WHEN D.Date = MD.Date THEN 1 ELSE 0 END AS IsMaxDate
FROM Data D
CROSS APPLY ( SELECT TOP 1 Date FROM Data ORDER BY Date DESC ) MD

+1

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Phil Parkin SSC Guru Points: 246986 More actions · Answer 4

Alan.B (4/20/2016)
Con Alexis (4/20/2016)
I'd probably use a CROSS APPLY in this instance...
e.g.
WITH Data AS
(
SELECT 1 AS ID, '1/1/1900' AS Date
UNION ALL SELECT 2, '1/1/1900'
UNION ALL SELECT 3, getdate()
UNION ALL SELECT 4, Getdate()
)
SELECT D.*, CASE WHEN D.Date = MD.Date THEN 1 ELSE 0 END AS IsMaxDate
FROM Data D
CROSS APPLY ( SELECT TOP 1 Date FROM Data ORDER BY Date DESC ) MD
+1

It seems that you are torn between CROSS APPLY and CROSS JOIN on this thread, Alan 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

j-1064772 SSCertifiable Points: 5316 More actions · Answer 5

All the solutions proposed are interesting.

However, would it not just be simpler to - as proposed by OP - to just store the MAX(date) into a local variable and simply write

DECLARE @d_MaxDate datetime2;

SELECT @d_MaxDate = MAX(SomeDate) FROM SomeTable;

UPDATE SomeTable SET IsMaxDate = CASE WHEN SomeDate = @d_MaxDate THEN 1 ELSE 0 END;

Just because it could be done in a single (more complicated) T-SQL statement might not necessarily mean it should be done this way.