Image this dataset
+----+---------+---------+--------+------------+
| id | user_id | product | event | timestamp |
+----+---------+---------+--------+------------+
| 1 | 1 | car | event1 | 1629992101 |
| 2 | 2 | car | event2 | 1629992102 |
| 3 | 1 | bike | event1 | 1629992103 |
| 4 | 1 | bike | event1 | 1629992104 |
| 5 | 2 | bike | event3 | 1629992105 |
| 6 | 2 | car | event1 | 1629992106 |
+----+---------+---------+--------+------------+
I want to be able to, for a certain user_id, get 1 row per product, being it the row where the timestamp is highest. So, for example, for user_id 2, the output would be:
+----+---------+---------+--------+------------+
| id | user_id | product | event | timestamp |
+----+---------+---------+--------+------------+
| 5 | 2 | bike | event3 | 1629992105 |
| 6 | 2 | car | event1 | 1629992106 |
+----+---------+---------+--------+------------+
For user_id 1, the output would be:
+----+---------+---------+--------+------------+
| id | user_id | product | event | timestamp |
+----+---------+---------+--------+------------+
| 1 | 1 | car | event1 | 1629992101 |
| 4 | 1 | bike | event1 | 1629992104 |
+----+---------+---------+--------+------------+
I got as far as:
SELECT * FROM `table` WHERE `timestamp` IN (SELECT MAX(`timestamp`) FROM `table` WHERE (`user_id` = ?) GROUP BY `product`)
but that seems to have some erratic result in some cases.
Update:
The question got closed because of a duplicate. In my opinion, that 'duplicate' is similar, but different. In my case, I want extra filtering to happen, being: first make sure you only look at the correct user_id, and then start grouping the rows. The linked duplicate doesn't support that, and it's key for me that that extra clause happens at the right time, to avoid SQL is grouping stuff from irrelevant user_ids that will be ditched later.
