1

I have a messages table as follows:

group_messages table

Basically what I want is, I want to fetch n users who sent the most recent messages to a group. So it has to be grouped by from_user_id and sorted by id in descending order. I have the following query:

SELECT `users`.`id` AS `user_id`, `users`.`username`, `users`.`image` 
FROM `group_messages`
JOIN `users` ON `users`.`id` = `group_messages`.`from_user_id`
WHERE `group_messages`.`to_group_id` = 31
GROUP BY `users`.`id`
ORDER BY `group_messages`.`id` DESC;

The problem with this is, when I group by user.id, the row with the smallest id field is taken into account. Therefor what I get is not in the order which id is descending.

So is there a way to group by, taking the greatest id into account ? Or should I approach it another way ?

Thanks in advance.

Edit: I think I got it.

SELECT `x`.`id`, `users`.`id` AS `user_id`, `users`.`username`, `users`.`image` 
FROM (SELECT * FROM `group_messages` ORDER BY `group_messages`.`id` DESC) `x`
JOIN `users` ON `users`.`id` = `x`.`from_user_id`
WHERE `x`.`to_group_id` = 31
GROUP BY `users`.`id`
ORDER BY `x`.`id` DESC;

Just had to make a select from an already ordered list.

OguzGelal
  • 757
  • 7
  • 20

1 Answers1

0

Using order by inside subquery is not a very efficient way. Try this:

users table
| uid | name | ---------- |

messages table
|id | from_uid | to_group_id | ----------- |

    SELECT u.* FROM users as u JOIN (
    SELECT g1.*
    FROM messages g1 LEFT JOIN messages g2
    ON g1.from_uid = g2.from_uid AND g1.to_groupid = g2.to_groupid 
      AND g1.id<g2.id
    WHERE g2.id is NULL) as lastmessage
    ON lastmessage.from_uid = u.uid
    WHERE lastmessage.to_groupid = 1
    ORDER BY lastmessage.id DESC;

Its not good to have order by in subqueries because they will make the queries slow and in your case u were running it on the whole table .

Check this out Retrieving the last record in each group.

Community
  • 1
  • 1
Deepak Puthraya
  • 1,325
  • 2
  • 17
  • 28