Find unique columns with criteria

Question

I have an SQL table that is written to by several external users. Each user has its own id and logs a single message at a time. Each row in this table has an auto-increment field called message_id. In other words, each row in the table has its own unique identifier. So if one were to sort the table by the message_id column, he would get all the messages sorted in chronological order.

Is there a single SQL command that can return the latest messages logged by each user?

I can do this in two steps:

1. Get a list of user_ids.

2. For each user_id:

   SELECT * 
   FROM myTABLE AS T WHERE T.user_id=user_id 
   ORDER BY message_id DESC 
   LIMIT 1
   

But I think there is a better way.

Thanks

Answer 1

One method is a join or a where condition:

select t.*
from mytable t
where t.id = (select max(t2.id) from mytable t2 where t2.user_id = t.user_id);

For best performance, you want an index on mytable(user_id, id).

Without such an index, you are possibly best off with an explicit join:

select t.*
from mytable t join
     (select max(id) as maxid
      from mytable t2
      group by user_id
     ) tt
     on t.id = tt.maxid;

Answer 2

If I'm understanding your question correctly, you are trying to execute a single query that will grab a limited number of results for each user_id. I found this question which might be helpful.

Using LIMIT within GROUP BY to get N results per group?

Edit: After studying this a bit more, it seems as if this is a complicated thing to achieve in MySQL. Unless you are struggling with ways to look for performance increases, I think you'd do just fine with a loop to query the table for each user you need data for. Of course, it depends on how much data you have and how many users there are.