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'.

Answer 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;

Answer 2

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

Answer 3

SELECT max( GREATEST(col1,col2, col3,col4, col5, col6, col7, col8, col9,col10, col11, col12)) AS 'MAXDATE' FROM table_name

Answer 4

Consider we have three tables C1,C2 and C3.

The table data are as follow :-

To find the 1st max value we use the following sql query:

Query:

;WITH CTE AS
(
SELECT (T.Marks) AS Highest, ROW_NUMBER() OVER (ORDER BY  MAX(T.MARKS) DESC) AS R FROM
(
SELECT (c1.Marks) AS Marks FROM c1 
UNION
SELECT (c2.Marks) AS Marks FROM c2 
UNION 
SELECT (c3.Marks) AS Marks FROM c3 
) AS T   GROUP BY (T.Marks) )
SELECT Highest FROM CTE WHERE R = 1

To find the next max values change the value of R in last line of query.