FLAG 'Y' to first matched condition in SQL Server

Question

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.

nbk · Answer 1 · 2023-03-28T15:46:54.790

you can check for the existence of another column prior of the actual

Select * 
  , case when ([Order] >= 3 and Amount >=400
  AND  NOT EXISTS( SELECT 1 FROM TableA WHERE ID = t.ID AND [Order] < t.[Order] AND [Order] >= 3 and Amount >=400)) then 'Y' else '' end as FLAG 
  
  from TableA t

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

fiddle

score 1 · Answer 2 · answered Mar 28 '23 at 15:28

We can use exists logic here:

SELECT ID, AMOUNT, [Order],
       CASE WHEN [Order] >= 3 AND AMOUNT > 400 AND
            NOT EXISTS (
                SELECT 1
                FROM TableA t2
                WHERE t2.ID = t1.ID AND
                      t2.[Order] < t1.[Order] AND
                      t2.AMOUNT > 400
            )
            THEN 'Y' END AS Flag
FROM TableA t1
ORDER BY ID, [Order];

score 1 · Accepted Answer · answered Mar 28 '23 at 15:42

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];

DBFiddle demo

FLAG 'Y' to first matched condition in SQL Server

3 Answers3