FLAG 'Y' to first matched condition in SQL Server

March 28, 2023

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.

>Solution :

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