Q:

MySQL Get exactly one entry per id by multiple ids

my problem is that I want this:
SELECT * FROM table
WHERE userId = 7243
ORDER BY date desc LIMIT 1

But for multiple ids in one request.
I tried this:
SELECT * FROM table
WHERE userId IN (7243, 1)
GROUP BY userId
ORDER BY date desc

But the order by seems to be ignored. Do anyone has a solution for me? Thank you

A:

If you want the max date record for each of the two IDs, then you may use a subquery:
SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT userId, MAX(date) AS max_date
    FROM yourTable
    WHERE userId IN (7243, 1)
    GROUP BY userId
) t2
   ON t1.userId = t2.userId AND t1.date = t2.max_date
WHERE
    t1.userId IN (7243, 1);

This is the just greatest-value-per-group question with a slight twist, namely that you only want to see two of the possible groups in the output.
As @Raymond commented below, an index on (userId, date) should greatly speed up the t2 subquery.  I am not sure if this index would help beyond that, but it should make a difference.

