SQL Max of Multiple Columns

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

SQL MAX of multiple columns and retrieve each row

This should be fastest and simplest:

(SELECT 'kills' AS what, kills, gamemode, id   FROM matches ORDER BY kills DESC, id LIMIT 1)
UNION ALL
(SELECT 'deaths'       , deaths, gamemode, id  FROM matches ORDER BY deaths DESC, id LIMIT 1)
UNION ALL
(SELECT 'assists'      , assists, gamemode, id FROM matches ORDER BY assists DESC, id LIMIT 1)
--  more ...

db<>fiddle here

Add id as second ORDER BY expression. This way, if multiple rows tie for the highest score, the row with the smallest id is chosen.

All parentheses are required. See:

• Combining 3 SELECT statements to output 1 table
• Create a unique index on a non-unique column

If any of the ORDER BY columns can be NULL, add NULLS LAST. See:

• Sort by column ASC, but NULL values first?

Finding max value of multiple columns in Sql

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;

Find max, second max and min value out of multiple columns

For SQL Server 2012+:

Another possible approach to get your expected output using ORDER BY with OFFSET and FETCH:

Table:

CREATE TABLE #Dimensions (
    [length] numeric(10, 2),  
    [width] numeric(10, 2),   
    [height] numeric(10, 2)
)
INSERT INTO #Dimensions
    ([length], [width], [height])
VALUES
    (5.60,    3.70,    0.90),
    (13.50,   6.54,    3.50),
    (14.33,   7.95,    3.86),
    (6.42,    6.69,    7.95),
    (12.00,   10.00,   9.00),
    (5.60,    3.70,    3.70)

Statement:

SELECT c.*
FROM #Dimensions d
CROSS APPLY (
    SELECT 
        [length] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY),
        [width] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY),
        [height] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY)
) c

Output:

----------------------
length  width   height
----------------------
5.60    3.70    0.90
13.50   6.54    3.50
14.33   7.95    3.86
7.95    6.69    6.42
12.00   10.00   9.00
5.60    3.70    3.70

For SQL Server 2008+:

When ORDER BY with OFFSET and FETCH is not supported, approach using ROW_NUMBER() is also a solution:

SELECT 
    [length] = c1.N,
    [width] = c2.N,
    [height] = c3.N
FROM #Dimensions d
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c1
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c2
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c3
WHERE (c1.RN = 1) AND (c2.RN = 2) AND (c3.RN = 3)

finding the max in a dataframe with multiple columns in pyspark

Why not use partition by instead of group by, that way you can keep all your columns. You will retain all your records.
Edit added- If you want the distinct values of A,C - just get the columns you want and get unique values.

import pyspark.sql.functions as F
from pyspark.sql.window import Window
table1 = table.withColumn("max_D",F.round(F.max('D').over (Window.partitionBy('A','C'))))
table1.select('A','B','C','max_D').distinct().show()

SUM UP two columns and then find the find MAX value in SQL Server

Try this - just sort by the Total column in a descending fashion, and take the first row in the result:

SELECT TOP (1)
    Q1.E_ID, Q1.E_Name, Q1.Total
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0) + ISNULL(T2, 0) AS Total
     FROM    
         table1) AS Q1
ORDER BY
    Q1.Total DESC;

Select records using max values for two columns

Quick answer, use NOT EXISTS to verify the same id has no other row with a later year or same year but later month:

select v1.*
from VENDORMONTHLY v1
where not exists (select 1 from VENDORMONTHLY v2
                  where v2.Vendor = v1.Vendor
                    and (v2.Year > v1.year
                      or (v2.Year = v1.Year and v2.Month > v1.Month)))

Will return both rows in case of a latest row tie.

Core ANSI SQL-99. Will run on any dbms!

BigQuery SQL - Create New Column Based on the Max Value from Multiple Columns

Consider below approach

select Cust_ID, if(count(1) = any_value(all_count), 'unknown', string_agg(type, ' and ')) freq_apple_type_buy
from (
  select *, count(1) over(partition by Cust_ID) all_count
  from (
    select Cust_ID, replace(arr[offset(0)], 'apple_', '') type,cast(arr[offset(1)] as int64) value
    from data t,
    unnest(split(translate(to_json_string((select as struct * except(Cust_ID) from unnest([t]))), '{}"', ''))) kv,
    unnest([struct(split(kv, ':') as arr)])
  )
  where true qualify 1 = rank() over(partition by Cust_ID order by value desc)
)
group by Cust_ID    

if applied to sample data in your question - output is

---

Related Topics