Selecting last record from inner joined records

Question

I made a post 6 days ago but no one seemed to anwser correctly and it was eventually abandoned so here I post it again (sorry for DPing but this is important to me) I have 2 tables - people (query is based on people.id so no need to ss the entire table) and messages (http://prntscr.com/94iq2e)

I have a query which is grouping messages with people and that is working fine but each person is grouped with the first message he sent and I need to make it so that it groups people with the LAST message they sent

Here is the query which is grouping people with the FIRST message

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

Link to the previous topic -> Selecting last record from INNER JOIN and grouping

You really need to parameterize your queries and avoid sql injection. — Sean Lange, Nov 19 '15 at 14:21

score 1 · Answer 1 · answered Nov 19 '15 at 14:23

1

You could use a subquery that returns the maximum date:

SELECT sender_id, MAX(date) AS max_date
FROM messages
GROUP BY sender_id

and join people table with this subquery, and then join to the messages table to get the message with the maximum date:

SELECT
   people.id, 
   people.avatar, 
   people.firstname, 
   people.lastname, 
   LEFT(messages.message, 90) AS message, 
   messages.seen, 
   messages.date 
FROM
   people INNER JOIN (
     SELECT sender_id, MAX(date) AS max_date
     FROM messages
     GROUP BY sender_id
   ) lm ON people.id = lm.sender_id
   INNER JOIN messages 
   ON people.id = messages.sender_id AND
      lm.max_date=messages.date
WHERE
   reciever_id = ...
ORDER BY
   ...

answered Nov 19 '15 at 14:23

fthiella

48,073
15
90
106

SELECT people.id, people.avatar, people.firstname, people.lastname, LEFT(lm.message, 90) AS message, lm.seen, lm.date FROM people INNER JOIN ( SELECT sender_id, MAX(date) AS max_date FROM messages GROUP BY sender_id ) lm ON people.id = lm.sender_id INNER JOIN messages ON people.id = messages.sender_id WHERE reciever_id = '". $user_data['id'] ."' GROUP BY messages.sender_id I get the 1054 error – Champa Nov 19 '15 at 14:39
@Champa it's not lm.message, lm.seen, ... please try with messages.message, messages.seen, etc. (see my updated answer, I quickly fixed it) – fthiella Nov 19 '15 at 14:45
Oh God.. I'm in such a hurry that i even didnt notice that lol Anyways SELECT people.id, people.avatar, people.firstname, people.lastname, LEFT(messages.message, 90) AS message, messages.seen, messages.date FROM people INNER JOIN ( SELECT sender_id, MAX(date) AS max_date FROM messages GROUP BY sender_id ) messages ON people.id = messages.sender_id INNER JOIN messages ON people.id = messages.sender_id AND messages.max_date=messages.date WHERE reciever_id = '". $user_data['id'] ."' GROUP BY messages.sender_id - ERROR 1052 – Champa Nov 19 '15 at 15:04

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

I would recommend using a sub query to pull out the latest messages. The below link may of help to you, it details a few possible solutions.

SQL join: selecting the last records in a one-to-many relationship

Cheers, Bob

edited May 23 '17 at 12:30

Community

1
1

answered Nov 19 '15 at 14:23

Robert Kitching

190
1
2
10

score 0 · Answer 3 · answered Nov 19 '15 at 14:28

I dont know mysql but in TSQL it would be like this to get last message:

ISNULL(SELECT TOP 1 messages.message FROM messages WHERE people.id = messages.sender_id ORDER BY messages.id DESC,'')

Whole code would look like this:

 SELECT     people.id, 
   people.avatar, 
   people.firstname, 
   people.lastname, 
   ISNULL(SELECT TOP 1 messages.message FROM messages WHERE people.id = messages.sender_id ORDER BY messages.id DESC,'') 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

Selecting last record from inner joined records

3 Answers3