Dave Dave - 1 month ago 6
MySQL Question

How to query a database table for records matching a predicate

I am new in sql and I need some help. I have a table with 4 columns

| id | action | date | session |
+------+--------+---------------------+------------+
| 1 | action_1 | 2016-10-01 09:00:00 | 1111 |
| 1 | action_2 | 2016-10-01 15:00:00 | 1111 |
| 1 | xxxx | 2016-10-01 19:10:00 | 1111 |
| 1 | action_4 | 2016-10-01 20:00:00 | 1111 |
| 1 | action_1 | 2016-11-02 13:00:00 | 1122 |
| 1 | yyyy | 2016-11-02 16:00:00 | 1122 |
| 1 | action_3 | 2016-11-02 23:00:00 | 1122 |
| 2 | action_1 | 2016-11-01 20:30:00 | 2222 |
| 2 | action_2 | 2016-11-01 20:50:41 | 2222 |
| 2 | yyyy | 2016-11-01 21:00:00 | 2222 |
| 2 | action_4 | 2016-11-01 21:30:00 | 2222 |
| 3 | action_1 | 2016-11-04 09:50:00 | 3333 |
| 3 | action_2 | 2016-11-04 10:00:00 | 3333 |
| 3 | action_3 | 2016-11-04 10:10:00 | 3333 |
| 3 | action_4 | 2016-11-04 10:25:00 | 3333 |
| 3 | action_5 | 2016-11-04 10:40:00 | 3333 |
| 3 | xxxx | 2016-11-04 11:00:00 | 3333 |
| 3 | action_7 | 2016-11-04 15:00:00 | 3333 |


("xxxx" and "yyyy" are any actions which should be found).
The question is: number of pre last actions.

Thanks in advance.

Answer

If "pre last action" is the "next to last action based on time", ...

  1. Find some code to do "groupwise max" to find the last two.
  2. Apply groupwise max again to find the min. This gives you just the "pre last action" rows.
  3. Do the GROUP BY to get the final count.

This will be about 5 nested SELECTs - 2 for each groupwise max, and one more (the outermost) to get the counts.

Are you sure to you want to go to that much trouble? I don't. Maybe someone else can take over an construct the query.