Finding maximum value across multiple columns in SQL

Data and Databases
1 
Hey everyone! I'm stuck on a SQL problem and could use some help. I've got a table with a bunch of columns, including a number, three date fields, and a cost. What I'm trying to do is get one row for each entry, but instead of all three dates, I just want the most recent one.

Here's what my table looks like:

ItemID | Date1 | Date2 | Date3 | Price

And I'm hoping to end up with something like this:

ItemID | LatestDate | Price

Any ideas on how to write a query to do this? I've been scratching my head for a while now and can't figure it out. Thanks in advance for any tips!
2

I’d recommend using a CASE statement for this scenario. It offers more flexibility and readability, especially when dealing with potential NULL values:

SELECT ItemID,
CASE
WHEN Date3 >= Date2 AND Date3 >= Date1 THEN Date3
WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
ELSE Date1
END AS LatestDate,
Price
FROM YourTable

This approach allows you to explicitly define the logic for determining the latest date. It’s particularly useful if you need to add more complex conditions in the future. Additionally, it performs well on large datasets.

3

oh interesting problem! have you tried using the GREATEST function? it can compare multiple columns and return the highest value. something like:

SELECT ItemID, GREATEST(Date1, Date2, Date3) AS LatestDate, Price
FROM YourTable

would that work for what youre trying to do? let me know if you need any clarification!

4

hey there! another way to tackle this is with a subquery:

SELECT ItemID,
(SELECT MAX(d) FROM (VALUES (Date1), (Date2), (Date3)) AS dates(d)) AS LatestDate,
Price
FROM YourTable

this approach is flexible and can handle null dates too. hope it helps!