How to specified max and min value from query?

Question

I have table like this and I need to specified max and min price in table.

id   value   price
1     aaa     100
2     bbb     200
3     ccc     300

Like this:

id   value   price   status
1     aaa     100     min
2     bbb     200     
3     ccc     300     max

With this code I can get max and min price value:

SELECT
    min(price) as min_price
    , max(price) as max_price
FROM 
(
    select *
    From MyTable
) tmp;

Answer 1

You need to use the max, min window functions as follows.

declare @Test table (id int, [value] varchar(3), price money);

insert into @Test (id, [value], price)
values
(1, 'aaa', 100)
, (2, 'bbb', 200)
, (3, 'ccc', 300);

select *
  , case when max(price) over () = price then 'max'
  when min(price) over () = price then 'min'
  else '' end status
from @Test;

Returns:

id  value   price   status
1   aaa     100.00  min
2   bbb     200.00  
3   ccc     300.00  max

This does not take into account ties however, you would need to specify what you want to happen in such a sitation.

Note: If you can please setup your questions with the DDL/DML as demonstrated here in future - it saves time for the person answering.