Finding max value of multiple columns in Sql

Question

How can I find maximum value on multiple columns. This is what I have so far.

With Temp AS (
    SELECT P.ID AS 'Product_ID',
           P.ProductCode AS 'Product_Code',
           P.Name AS 'Product_Name',
           P.SellPrice AS 'SellPrice',
           P.SellPrice+(P.SellPrice*TVA/100) AS 'PricePerUnit',
           P.TVA AS 'TVA',
           P.Discount AS 'Discount_Product',
           0 AS 'Discount_Code',
           0 AS 'Discount_Newsletter',
           V.ID AS 'Variant_ID',
           V.Nume AS 'Variant_Name',
           V.Stock-V.Reserved AS 'Quantity_MAX',
           T.Quantity AS 'Quantity',
           I.ImageName AS 'Image',
           0 AS 'Is_Kit'
    FROM TemporaryShoppingCart T
    INNER JOIN ProductVariant V ON V.ID=T.Variant_ID
    INNER JOIN Product P ON P.ID=V.ProductID
    LEFT JOIN ProductImage I  ON I.ProductID=P.ID AND DefaultImage=1
    WHERE T.ID=@ID AND T.Variant_ID!=0

 ) SELECT t.* ,MAX(MAXValue) FROM (SELECT (T.Discount_Product) AS 'MAXValue' 
                               UNION ALL 
                               SELECT (T.Discount_Code) 
                               UNION ALL 
                               SELECT (T.Discount_Newsletter)) as 'maxval' //error 
   FROM Temp T

This code is giving me the error: Incorrect syntax near 'maxval'.

Solution 1

Are you simply looking for GREATEST?

SELECT
  t.*, 
  GREATEST(T.Discount_Product, T.Discount_Code, T.Discount_Newsletter) as 'maxval' 
FROM Temp T;

However GREATEST Returns NULL when a value is NULL, so you might want to care about this, too. For instance:

SELECT 
  t.*, 
  GREATEST
  (
    coalesce(T.Discount_Product,0), 
    coalesce(T.Discount_Code, 0), 
    coalesce(T.Discount_Newsletter, 0)
  ) as 'maxval' 
FROM Temp T;

EDIT: In case GREATEST is not available in your dbms you can use a case expression.

SELECT 
  t.*, 
  CASE 
    WHEN coalesce(T.Discount_Product, 0) > coalesce(T.Discount_Code, 0)
     AND coalesce(T.Discount_Product, 0) > coalesce(T.Discount_Newsletter, 0)
     THEN coalesce(T.Discount_Product, 0)
    WHEN coalesce(T.Discount_Code, 0) > coalesce(T.Discount_Product, 0)
     AND coalesce(T.Discount_Code, 0) > coalesce(T.Discount_Newsletter, 0)
     THEN coalesce(T.Discount_Code, 0)
    ELSE coalesce(T.Discount_Newsletter, 0)
  END
FROM Temp T;

EDIT: To get your own statement syntactically correct, do:

SELECT 
  t.*,
  (
    select MAX(Value) 
    FROM 
    (
      SELECT T.Discount_Product AS Value 
      UNION ALL 
      SELECT T.Discount_Code
      UNION ALL 
      SELECT T.Discount_Newsletter
    ) dummy -- T-SQL requires a name for such sub-queries
  ) as maxval
FROM Temp T;

Solution 2

You probably want to have each Select statement to have a From clause.