gattra gattra - 2 months ago 6
SQL Question

Get max value of column after query results - sql

This isn't a standard

MAX(column_name)
answer I don't think.

Basically, I want the query to return a table with an added column
MaxTotalRev
. That column should only be populated IF it is the row with the max value for TotalRev. All other rows should have this column be empty.

This should not affect the order by which the results are returned. Instead it should just be an additional column on the returned table.

i.e

total_rev | max_total_rev
-------------------------
1 | null
3 | 3
2 | null

Answer

Here's one option using a window function -- max / over -- with a case statement:

select total_rev,
  case when total_rev = max(total_rev) over () then total_rev end as max_total_rev 
from yourtable