Max of multiple columns in SQL

Question

I have found this great solution from an old stack post: https://stackoverflow.com/a/6871572/194653

But for some reason it is not working for me, this is the query that I need to execute to get the max value:

Select a.id as id, first_name as name, surname, h.maiden_name, c.salutation, c.gender, coalesce (d.marital_status,’Z’) as marital_status, coalesce (g.occupation, ‘0047’) as occupation, coalesce (f.kind_of_work, '9') as kind_of_work,‘’ as change_type
(SELECT Max (date_last_mod)
FROM (VALUES (a.date_last_mod), (c.date_last_mod),(d.date_last_mod),
(f.date_last_mod), (g.date_last_mod), (h.date_last_mod)) AS value (date_last_mod)) as date_last_mod

from people.al_names_spk a

inner join people.al_salutation_spk c
on a.id = c.id
left join people.al_marital_status_spk d
on a.id = d.id
left join people.al_kind_of_work_spk f
on a.id = f.id
inner join people.al_occupation_spk g
on a.id = a.id
left join people.al_maiden_name_spk h
a.id = h.id
LEFT SEMI JOIN people_st.stg_person_hist_spk b ON (a.id = b.id AND b.data_date_part = '2023-06-01’)

I have also tried this other possible solution to select the max values but it didn't work:

(SELECT MAX(date_last_mod) FROM (
    SELECT a.date_last_mod
    UNION ALL
    SELECT c.date_last_mod
    UNION ALL
    SELECT d.date_last_mod
    UNION ALL
    SELECT f.date_last_mod
    UNION ALL
    SELECT g.date_last_mod
    UNION ALL
    SELECT h.date_last_mod ) AS value(date_last_mod)) AS date_last_mod

Am I doing something wrong? Everytime I execute this I have a syntax error message. I have to say that I am executing this in HUE.

UPDATE: The error message:

AnalysisException: Syntax error in line 37:undefined: (SELECT Max(date_last_mod) ^ Encountered: SELECT Expected: ALL, CASE, CAST, DEFAULT, DISTINCT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error

Thanks for reading.

Hi - please update your question with the error message you are getting. Given that the solution you've linked to explicitly mentions SQL Server, it may well be that the code is specific to that DBMS rather than generic SQL that will run in your DBMS — NickW, Jun 28 '23 at 12:37
Not sure why you're trying to use [`VALUES`](https://www.postgresql.org/docs/current/queries-values.html) here, since it's normally meant to provide constants, not to use values obtained from an existing table or query. — Bruno, Jun 28 '23 at 12:54

score 2 · Answer 1 · answered Jun 28 '23 at 12:52

You can use GREATEST to get the maximum value of multiple column in the same row.

Then, you can use MAX to aggregate the maximum on each row (computed by GREATEST) into the overall MAX over the rows you want.

SELECT MAX(GREATEST(a.date_last_mod, b.date_last_mod, c.date_last_mod))
FROM table_a a
    LEFT JOIN table_b b ON a.id=b.id
    LEFT JOIN table_c b ON a.id=c.id

Depending on how you write your overall query, you could also do it the other way around: compute the GREATEST of each MAX.
(In some cases, I suspect that might perform better, since MAX per column first may be able to use index-based optimisation if those columns are individually indexed. That said, it depends on your indexes.)

By the way, I wasn't sure what your overall query was, but it looks like you may just need `GREATEST` if you want the maximum value per (joined) row. — Bruno, Jun 28 '23 at 14:13

Max of multiple columns in SQL

1 Answers1