Finding rows with latest date by id mysql

I have these 2 tables I am trying to write a query that will help me select all rows that gives this result

users

id	name
1	test
2	test2

logs

id	userId	message	date
1	1	this is a test	2020-10-07 12:57:14
2	1	another reason	2020-10-07 13:57:14
3	1	another reason 2	2020-10-07 14:57:14
4	2	another reason 3	2020-10-04 12:57:14
5	2	another reason 4	2020-10-05 12:57:14
6	2	another reason 4	2020-10-06 12:57:14

Output Table
I need to pass many user Ids like in this case (1,2) and get below table only return MAX (date) per row per userId

id	userId	message	date
3	1	another reason 2	2020-10-07 14:57:14
6	2	another reason 4	2020-10-06 12:57:14

Is this possible with one Query? This what I have but not working

SELECT 
id ,
userId ,
message,
date
FROM logs
WHERE userId IN (1,2)
ORDER BY date DESC;

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You may use the results of a window function ROW_NUMBER to retrieve these results.

SELECT
    id,userId,message,date
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY userId
            ORDER BY date DESC
        ) as rn
    FROM 
        logs
) t
WHERE rn=1 AND
      userId IN (1,2)
ORDER BY date DESC

and for older mysql versions

SELECT
    id,userId,message,date
FROM (
    SELECT 
        l.*,
        @row_num:=IF([email protected]_user_id,@row_num+1,1) as rn,
        @prev_user_id:=userId
    FROM 
        logs l
    CROSS JOIN (
        SELECT @row_num:=0, @prev_user_id:=NULL
    ) as vars
    ORDER BY userId, date DESC
    
) t
WHERE rn=1 AND
      userId IN (1,2)
ORDER BY date DESC

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating