SQL: return rows with only the earliest date for each id but only if it satisfies condition-CodePudding

I would like to get list of unique id that have 'condition=1' before 'condition=2'.

id	date	condition1	condition2
1	2022/02	1	0
1	2022/04	0	1
1	2022/05	0	0
2	2021/09	0	1
2	2022/01	1	0
3	2022/02	1	0
3	2022/05	0	1

In this case it would be 1 and 3.

SELECT id, MIN(date) FROM TABLE GROUP BY id

I know that i can do something like this to get first dates for id but i just cant figure out what to do for my problem

CodePudding user response：

Something like:

SELECT DISTINCT Id
FROM
  (SELECT id, MIN(date)
  FROM TheTable
  WHERE Condition1 = 1
  GROUP BY Id) c1
INNER JOIN
  (SELECT Id, MIN(date)
  FROM TheTable
  WHERE Condition2 = 1
  GROUP BY Id) c2
ON c1.Id=C2.Id AND c1.Date < c2.Date

CodePudding user response：

We can GROUP BY id and build two conditional MIN dates using CASE WHEN.

In the HAVING clause we say that the minimum date with condition 1 must appear before the minimum date with condition 2.

SELECT id
FROM yourtable
GROUP BY id
HAVING MIN(CASE WHEN condition1 = 1 THEN date END) < 
MIN(CASE WHEN condition2 = 1 THEN date END)
ORDER BY id;

Try out here: db<>fiddle