Finding rows with latest date by id mysql-CodePudding

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;

CodePudding user response：

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(userId=@prev_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