When a row has a MAX value then get it a string value of "Yes"?

Question

I think this should be a quick one, but how do I get to identify a MAX value of a table and give it a string label "Yes"?

I know there is a case statement in this but just need some direction.

Example

ID      Amount   Max
110      1000     
111      1000
112      2000    Yes
113      1000

Answer 1

You can use window functions:

select 
    t.*,
    case when amount = max(amount) over () then 'Yes' end
from mytable t

Some RDBMS do not like the empty over() clause, in which case you can use rank() instead:

select 
    t.*,
    case when rank() over(order by amount desc) = 1 then 'Yes' end
from mytable t

Demo on DB Fiddle:

 id | amount | case
--: | -----: | :---
110 |   1000 | null
111 |   1000 | null
112 |   2000 | Yes 
113 |   1000 | null

Answer 2

You can use sub-query if DBMS doesn't support window functions :

select t.*, 
      (case when t.amount = (select max(t1.amount) from table t1) then 'Yes' else '' end) as Max
from table t;

You can eliminate else clause in case if you want null instead.