Selecting last record from INNER JOIN and grouping

Question

Okay so I have a query that selects 1 message from each user that is ordered by date DESC but selects the first message for each group.. I need to select the last message by date from each group (each user) This is my query:

SELECT     people.id, 
           people.avatar, 
           people.firstname, 
           people.lastname, 
           LEFT(messages.message, 90) AS message, 
           messages.seen, 
           messages.date 
FROM       people 
INNER JOIN messages 
ON         people.id = messages.sender_id 
WHERE      reciever_id = '". $user_data['id'] ."' 
GROUP BY   sender_id 
ORDER BY   date DESC limit 11

That query is not grouped by date. It is grouped by sender_id. It doesn't show the first message of each group. It shows all messages in descending date order. — kainaw, Nov 12 '15 at 20:13

score 0 · Answer 1 · answered Nov 12 '15 at 22:05

0

Please excuse my syntax if it is a bit off, but I would probably try something like this.

SELECT people.id, 
       people.avatar, 
       people.firstname, 
       people.lastname, 
       LEFT(msg.message, 90) AS message, 
       msg.seen, 
       msg.date 
FROM  people 
INNER JOIN (SELECT message, seen, date, sender_id
                FROM messages GROUP By sender_id 
                ORDER By date DESC LIMIT 1) msg
                ON people.id = msg.sender_id
WHERE      reciever_id = '". $user_data['id'] ."'

answered Nov 12 '15 at 22:05

Hrafe

1

I get the #1054 - Unknown column in 'Field List' – Champa Nov 13 '15 at 14:02
Are you testing it directly against the database without that where clause? – Hrafe Nov 14 '15 at 10:16
No, "WHERE" is in the query – Champa Nov 14 '15 at 19:32

Selecting last record from INNER JOIN and grouping

1 Answers1

Linked