Table A :
ID AMOUNT Order
-------------------------
1 100 1
1 300 2
1 320 3
2 100 1
2 200 2
2 423 3
2 613 4
3 112 1
3 218 2
3 290 3
3 480 4
Expected Output : Table A :
ID AMOUNT Order Flag
------------------------------
1 100 1
1 300 2
1 320 3
2 100 1
2 200 2
2 423 3 Y
2 613 4
3 112 1
3 218 2
3 290 3
3 480 4 Y
I have a Table A which have duplicate IDs with multiple amount in some order. I want to display Flag Y to the first row that match the given condition. Condition is : Flag Y to the 1st row which have Amount greater or equal to 400 and Order greater or equal to 3.
The query I tried is :
Select * , case when (Order >= 3 and Amount >=400) then 'Y' else '' end as FLAG from TableA
But this query flagged all the rows that match the condition but I need to Flag only the first row that match this condition.
select t1.*, case when t2.id is not null then 'Y' else '' end as FLAG
from myTable t1
left join (
select id, min([Order]) minOrder
from myTable
where ( [Order] >= 3 and Amount >= 400)
group by id) t2 on t1.id = t2.id and t1.[Order] = t2.[minOrder];