gattra gattra - 10 months ago 48
SQL Question

Get max value of column after query results - sql

This isn't a standard

answer I don't think.

Basically, I want the query to return a table with an added column
. 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.


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

Answer Source

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