Igor Lessa Igor Lessa - 6 months ago 8
SQL Question

Select MIN of a function and create a virtual table

I'm needing to take the min value of a function, if the min value he put on a 0 virtal column, if not, place 1.

SELECT ID, Name, (X + Y) AS TOTAL, MIN(TOTAL) AS MINVALUE FROM TABLE


Example

ID Name TOTAL MINVALUE
1 item 22 0
1 item 33 1
1 item 50 1
2 item2 200 1
2 item2 30 0

Answer

You do this using window functions and case:

select id, name, (x + y) as total,
       (case when (x + y) = min(x + y) over (partition by id)
             then 0 else 1
        end) as IsNotMinValue
from t;

I normally follow the Unix convention of "0" being false and "1" (actually not-"0") being true. Hence, I named the flag IsNotMinValue because that is how I would interpret it.