Add a columns who tags the max and min value of a column

Question

I want to add an extra column, where the min and max values of each group (ID) will appear. Here how the table looks like:

select ID, VALUE from mytable

ID VALUE
1 4
1 1
1 7
2 2
2 5
3 7
3 3

Here is the result I want to get:

ID VALUE min_max_values
1 4 NULL
1 1 min
1 7 max
2 2 min
2 5 max
3 7 max
3 3 min
4 1 both
5 2 min
5 3 max

Thank you for your help in advance!

Answer 1

You can use window functions and a case expression:

select id, value,
    case
        when value = min_value and min_value = max_value then 'both'
        when value = min_value then 'min'
        when value = max_value then 'max'
    end as min_max_values
from (
    select t.*,
        min(value) over(partition by id) as min_value,
        max(value) over(partition by id) as max_value
    from mytable t
) t

The subquery is not strictly necessary, we could use the window min() and max() directly in the outer query. It is just there to avoid repeatedly typing the window function expressions in the outer query.