CODEWITHSUNDEEP
sqlsql-serverfunction
Igor Lessa
Igor Lessa

Reputation: 47

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

Upvotes: 0

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 3

Related Questions