I’m trying to find the highest value between two columns in the same row, similar to how Math.Max works in programming languages. Here’s what I want to accomplish:
SELECT p.ProductId, GREATEST(p.CurrentPrice, p.OriginalPrice)
FROM Product p
I know that the standard MAX function is an aggregate function that works across multiple rows, not for comparing values within the same row. I need something that can compare two column values and return the larger one for each individual record. What’s the best approach to achieve this behavior in SQL Server? I’ve seen some solutions using CASE statements but wondering if there’s a more elegant way to handle this scenario.
SQL Server doesn’t have a GREATEST function, but you can work around it with a CASE statement or the VALUES clause with MAX. I prefer the VALUES approach: SELECT p.ProductId, MAX(value) FROM Product p CROSS APPLY (VALUES (p.CurrentPrice), (p.OriginalPrice)) AS v(value). It basically creates a temp table with both values and runs MAX on them. Handles NULLs nicely since MAX ignores them, and it’s easy to add more columns later. I’ve used this tons of times in financial reports where you’re comparing different prices or rates.
interesting challenge! have you tried the IIF function? something like IIF(p.CurrentPrice >= p.OriginalPrice, p.CurrentPrice, p.OriginalPrice) might be cleaner than CASE. also, are you dealing with any NULL values in those columns? that could complicate things.