CODEWITHSUNDEEP
sqlsql-server
user6889814
user6889814

Reputation:

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

Upvotes: 0

Views: 480

Answers (1)

sgeddes
sgeddes

Reputation: 62841

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

Upvotes: 2

Related Questions