SQL query for determining which grouped row is True-CodePudding

I have the following database structure

datetime:datetime|id:id|result:bool|s_num:string|name:string

Sample of db

Datetime  |id  |result  | s_num  |name
__________________________________________
12.01.2022|2   |False   | a21    | test_1
13.01.2022|3   |False   | a22    | test_1
15.01.2022|51  |True    | a21    | test_1
16.01.2022|3   |False   | a22    | test_1
17.01.2022|3   |True    | a22    | test_1

Basically, I need to group every s_num then tell which entry of it (ordered by datetime where the oldest means first) is True. For provided Sample output would be

s_num  |name  |after_which
___________________________
a21    |test_1|2
a22    |test_1|3

Because the second row of s_num with value of a21 was True. I was thinking of lateral, but MariaDB doesn't support it. I tried numbering rows with ROW_COUNT() and over but my MariaDB version is too low. Furthermore, I can't think of any solution. If I could use python I would get all serial_numbers, then use for loop for every serial_number and check which row has value of True, but I want to use it for Grafana. For Grafana I was thinking about 10 queries, each for checking 1 row number.

CodePudding user response：

Although your question is not that very clear to me. Does this provide you your result?

SELECT 
  s.s_sum,
  s.name ,
  (SELECT COUNT(*) 1 FROM sample_db s1 WHERE s1.s_sum=s.s_sum AND s1.datetime<s.datetime AND s1.result=FALSE) after_which
FROM
  `sample_db` s 
  WHERE s.result=TRUE
GROUP BY s.s_sum