How to select the group of most recent records based on specific conditions in MySQL

user_12657624 0
Viewed 42

I am looking for a way to select group of most recent records. I have two tables:

Table1:

+---------+-------------+--------+--------+------------------+
| user_id | category_id | field1 | field2 |       date       |
+---------+-------------+--------+--------+------------------+
|      19 |           2 |      3 |      3 | 22/01/2021 15:00 |
|      19 |           1 |      1 |      3 | 22/01/2021 15:00 |
|      19 |           5 |      2 |      2 | 22/01/2021 15:00 |
|      18 |           1 |      2 |      2 | 22/01/2021 15:04 |
|      18 |          31 |      4 |      1 | 22/01/2021 15:04 |
|      18 |          40 |      3 |      1 | 22/01/2021 15:04 |
|      19 |          40 |      2 |      2 | 22/01/2021 15:21 |
|      19 |         166 |      1 |      1 | 22/01/2021 15:21 |
|      19 |          40 |      5 |      5 | 22/01/2021 16:23 |
|      19 |         166 |      6 |      6 | 22/01/2021 16:23 |
+---------+-------------+--------+--------+------------------+

Table2:

+---------+-------------+
| user_id | category_id |
+---------+-------------+
|      18 |           1 |
|      18 |          31 |
|      18 |          40 |
|      19 |          40 |
|      19 |         166 |
+---------+-------------+

I would like to select the most recent rows from Table1 for specific user_id and category_id that exist in Table2. For, example if user_id=19, I would like to get the following group of records:

+---------+-------------+--------+--------+------------------+
| user_id | category_id | field1 | field2 |       date       |
+---------+-------------+--------+--------+------------------+
|      19 |          40 |      5 |      5 | 22/01/2021 16:23 |
|      19 |         166 |      6 |      6 | 22/01/2021 16:23 |
+---------+-------------+--------+--------+------------------+

My the most closest query is given the working fiddle, but that gives me the last four records of Table1.

mysql sql
2 Answers

This seems to do what you want for all users:

SELECT T1.*
FROM Table1 t1
WHERE T1.DATE = (SELECT MAX(tt1.DATE)
                 FROM Table1 tt1 JOIN
                      Table2 tt2
                      ON TT1.user_id = Tt2.user_id AND
                         tt1.category_id = tt2.category_id
                 WHERE tt1.user_id = t1.user_id AND
                       tt1.category_id = t1.category_id
                )

You can add a filter in the outer query for a particular user.

user_11440350

I think the most efficient way is using Ties in sql :

SELECT TOP 1 WITH TIES t1.*
 FROM Table1 t1 join Table2 t2 on t1.user_id=t2.user_id and t1.category_id=t2.category_id
ORDER BY ROW_NUMBER() OVER(PARTITION BY t1.[user_id],t1.[category_id] ORDER BY t1.date desc)
user_54808870
Related