DenStudent DenStudent - 1 month ago 10
SQL Question

SQL Server - flag column for record with lowest value

I have following table:

ID | Number | ... |
1 | 100 | ... |
1 | 200 | ... |
2 | 400 | ... |
2 | 300 | ... |
2 | 500 | ... |
3 | 600 | ... |


I am trying to add a column that shows me which record holds the lowest
Number
value, grouped by ID.

So my result would be:

ID | Number | ... | IsLowest
1 | 100 | ... | 1
1 | 200 | ... | 0
2 | 400 | ... | 0
2 | 300 | ... | 1
2 | 500 | ... | 0
3 | 600 | ... | 1


Any suggestions/Ideas?

vkp vkp
Answer

Use min window function.

select id,number,
case when min(number) over(partition by id) = number then 1 else 0 end is_lowest
from t
Comments