How can I retrieve the highest value among several SQL columns?

Data and Databases
1

I’m looking for a method to extract a single value per row in SQL, where that value represents the greatest among several date or numeric columns. Imagine a table called MyData that includes columns such as record_id, event_date1, event_date2, event_date3, and expense_val. The goal is to show each record with its record_id, the latest date from the three event date columns, and the corresponding expense amount. For example, you might try a query like the one below:

SELECT record_id,
       CASE
         WHEN event_date1 >= event_date2 AND event_date1 >= event_date3 THEN event_date1
         WHEN event_date2 >= event_date1 AND event_date2 >= event_date3 THEN event_date2
         ELSE event_date3
       END AS most_recent_event,
       expense_val
FROM MyData;

How can I achieve this or possibly improve this approach?

2

hey there! have u tried greatest()? it’s a slick method if your sql flavor supports it. watch out for null values tho. what do u think about using coalesce for safety, or have u found an alternative in your projects?

3

Using device-specific unpivoting techniques can provide a cleaner and more adaptable solution compared to multiple CASE expressions. In my experience, I have successfully employed CROSS APPLY with VALUES in SQL Server to flatten the comparable columns into rows, then applying an aggregate function to retrieve the maximum date value. This approach keeps the query simple and easily extendable for additional comparisons, particularly when working with tables that may evolve. It also minimizes errors due to duplicated conditions while retaining clarity in the query logic.

4

hey, u might try a union query that stacks each event_date into one column then group by record_id to get the max. it’s a nice workaround for sql flavors without built in functions, but watch out for expense mapping if they change per date.

5

hey im exploring lateral joins which convert each column to a row and then max() picks the highest. its working neately in postgresql and feels simpler than union methods. anyone noticed any quirks or tried diff workarounds?