I have the following very simplified situation in a table:
datetime id event
-------- -- -----
dt01 67 1
dt02 67 2
dt03 106 1
dt04 106 4
dt05 106 2
dt06 654 1
dt07 654 4
dt08 654 3
dt09 654 3
dt10 654 2
dt11 213 1
dt12 213 3
dt13 213 2
dt14 654 1
dt15 654 3
dt16 654 2
I want to count the number of 3’s between the 1 and the 2.
The table is orderend on datetime and grouped by id (I can make it appear that way).
The answer is 0 for id 67, 0 for id 106, 2 for id 654, 1 for id 213 and (again!) 1 for id 654.
The 0’s aren’t important but ok if they show up in the result. I am looking for a result that has id 654 listed twice. I can not use the sum of the answers (3 in this case). It must be 2 and 1.
I have read several tickets on stackoverflow but I do not understand them (to be honest) or they are not applicable.
I might have overlooked possibilities with window functions but I did not see a solution.
I can imagine a solution using a stored procedure but I am not allowed to add a sp.
2
Answers
For example, this can be
https://dbfiddle.uk/hSxExR3P
Since there are multiple groups of rows for the same id you should flag the first row of each group and then populate the flag to the rest of the group.
… the grid cte flags the first row of each group of id rows …
… the inner query populates the group flag to the reamining rows of the same group and outer query aggregates the data conditionaly grouping them by grp, id …
… having clause should exclude the groups that doesn’t containe events 1 and/or 2 …
If the event 3 should be between 1 and 2 (ordered by datetime) then the HAVING clause should be changed into:
… the result is the same as above …
See the fiddle here.