SQL Server equivalent of Math.Max for comparing two column values in a single row

Data and Databases
1

I’m trying to figure out how to get the maximum value between two columns in the same row using SQL Server. In .NET I would use Math.Max to compare two values, but I can’t find something similar in SQL.

Here’s what I want to achieve:

SELECT p.ProductId, GREATEST(p.RetailPrice, p.WholesalePrice)
FROM Product p

But GREATEST doesn’t exist in SQL Server like it does in other databases. The regular MAX function is for aggregating multiple rows, not for comparing column values within the same row.

What’s the best approach to compare two column values and return the larger one in SQL Server? I need this for a report where I want to show the higher of two price columns for each product.

2

try using a CASE statement: CASE WHEN p.RetailPrice > p.WholesalePrice THEN p.RetailPrice ELSE p.WholesalePrice END. it works great and is way simpler than other methods.

3

The IIF function is way cleaner than CASE for this. Just use IIF(p.RetailPrice > p.WholesalePrice, p.RetailPrice, p.WholesalePrice) - same result, less code. I love this approach when I’m comparing multiple columns in one query since it keeps things readable. Watch out for NULL values though - both methods return NULL if either column is NULL. You’ll probably want to wrap your columns with ISNULL or COALESCE depending on what your business needs. Like this: IIF(ISNULL(p.RetailPrice,0) > ISNULL(p.WholesalePrice,0), ISNULL(p.RetailPrice,0), ISNULL(p.WholesalePrice,0)) - that way you get actual results even with missing data.

4

Nice solutions! What about using VALUES with a subquery? Like SELECT TOP 1 value FROM (VALUES (p.RetailPrice), (p.WholesalePrice)) AS v(value) ORDER BY value DESC. More verbose but wondering about performance differences on large datasets?