Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Robert Walker
Robert Walker
17,146 Points
Posted by Robert Walker
Robert Walker
17,146 Points

Return last message only from a SQL query

Select 

  user_id,
  display_picture,
  nickname,
  sender_id,
  receiver_id,
  message,
  date

From

  users Inner Join
  user_messages
    On user_messages.sender_id = users.user_id

Where  

  user_messages.receiver_id = ID

Order By

  date Desc

This works how it should, it returns all messages for a user by date Desc, what I want to do though is only return the last message per user.

So instead of getting all the messages I just want the last message from each user to that person.

Ive tried the group by user id but its not working, tried a few other things too but really struggling with it.

2 Answers

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi Robert,

When you tried GROUP BY, did you change your date column in the select portion to MAX (date)?

I would suggest changing date to MAX(date) in the SELECT portion.

Add GROUP BY user_id after your WHERE clause.

And take out the ORDER BY clause.

Update:

When I tested my above suggestion on a small amount of hand-entered data I was getting the max date for each sender to a particular user but I didn't realize you wouldn't necessarily be getting the correct message back that goes with that date.

I did some more research on this and found this stackoverflow answer: http://stackoverflow.com/a/16910259

Working off of that I came up with the following query:

SELECT
  user_id,
  nickname,
  sender_id,
  receiver_id,
  message,
  date
FROM
  users
INNER JOIN
  (SELECT t1.* FROM user_messages AS t1
    JOIN (SELECT sender_id, max(date) AS maxDate
            FROM user_messages
            WHERE receiver_id = 3
            GROUP BY sender_id) AS t2
    ON t1.sender_id = t2.sender_id AND t1.date = t2.maxDate) AS t3
ON t3.sender_id = users.user_id

Order By date Desc

I ran this against a mock database of 10 users and 3000 messages and used 3 for the receiver_id. I got back 10 results, meaning all the users sent messages to everyone else including themselves. I didn't verify if the results were correct but the 10 dates were all from the last few months and I had dates chosen from 2013 all the way to the present. So it seems plausible but if you can get this working on your data you should verify the accuracy.

I omitted some of the columns because I didn't set them up with the test data but you can add in what's missing.

These were my results:

8   Ashley  8   3   a feugiat et eros vestibulum ac est lacinia nisi    2016-12-06 21:27:44
9   Adam    9   3   arcu libero rutrum ac lobortis vel dapibus at   2016-12-03 23:20:04
1   John    1   3   vitae quam suspendisse potenti nullam porttitor lacus at turpis donec posuere metus vitae ipsum aliq    2016-11-30 17:46:45
3   Bill    3   3   tortor id nulla ultrices aliquet maecenas leo odio condimentum id luctus nec molestie sed justo 2016-11-26 19:42:51
2   Harry   2   3   duis ac nibh fusce lacus purus aliquet at feugiat non pretium quis lectus suspendisse potenti   2016-11-25 17:24:20
6   Brandon 6   3   cum sociis natoque penatibus et magnis dis parturient montes nascetur ridiculus mus 2016-11-21 13:18:31
4   Robert  4   3   proin eu mi nulla ac    2016-11-14 01:20:12
5   Steven  5   3   dolor quis odio consequat varius integer ac leo 2016-10-03 19:49:31
10  Jeremy  10  3   scelerisque mauris sit amet eros suspendisse accumsan tortor quis turpis sed ante vivamus tortor dui    2016-10-01 01:19:13
7   Jack    7   3   amet nulla quisque arcu libero  2016-09-03 00:58:13

I did verify through a separate query that Jack's last message to Bill (receiver) was indeed on 2016-09-03 and the message is correct. So it's possible that the query is correct assuming this is what you wanted.

Let me know if this gets you any closer.

Robert Walker
Robert Walker
17,146 Points
Robert Walker
Robert Walker
17,146 Points

I didn't but did try it and still no joy.

There is something a miss here because looking at the statement and my understanding of it, this should be working.

So here is my closest attempt so far:

Select
  users.user_id,
  users.display_picture,
  users.nickname,
  user_messages.sender_id,
  user_messages.receiver_id,
  user_messages.sender_viewable,
  user_messages.receiver_viewable,
  user_messages.message,
  user_messages.date
From
  users Inner Join
  user_messages
    On user_messages.sender_id = users.user_id
Where
  user_messages.receiver_id =NUMBER AND user_messages.receiver_viewable ='yes'
Group By
  users.user_id
Order By
  user_messages.date Desc

This outputs one message per user like I want but its the wrong message, I know from database there are newer messages for each of the users.

For example:

The top result shows a message from the user Harry and the message says "this is my second message" the dates says 21/11/2016 14:48:12

However that is not that latest message from Harry, the last message was 04/12/2016 12:38:41 and says "this is my last message"

The same for every other result, it only returns one message like I want but it doesn't return the correct message, its just random messages.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

What result were you getting when you used MAX(date)?

I'm not sure if I understand the query. Are you trying to get the last message from all users sent to a particular user?

For example, 5 different users all sent messages to user John. You want the the last message each of those 5 users sent to John?

Robert Walker
Robert Walker
17,146 Points
Robert Walker
Robert Walker
17,146 Points

It just returned one message from 2012.

Basically yes, its a messaging system, inbox as you will.

I wanted the last message from each user to message John.

The above query returns every message sent to John with the latest message at the top date Desc but when I try to only return the very last message from each user it just goes crazy.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hey Robert,

I've updated my answer which takes a different approach. See if that helps at all.

Robert Walker
Robert Walker
17,146 Points
Robert Walker
Robert Walker
17,146 Points

Thanks, this looks promising, just getting up but will check it asap once at the computer.

I was looking at those on stack the last couple of days but they totally confused me, will give this a try and let you know, thanks again for all the help though.

Robert Walker
Robert Walker
17,146 Points
Robert Walker
Robert Walker
17,146 Points

Still going to have to run a few more checks on it but that seems to of cracked it perfectly at the moment, 80 results and all messages seem to be correct.

Just going to check over every message and then up it to the server, thank you for all your help though, been annoying the hell out of me for ages, thank you!

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

You're welcome. Hopefully it all checks out.

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

:point_right: Try adding this to the WHERE clause:

AND date = (SELECT MAX(date) FROM user_messages WHERE sender_id = users.user_id)
Robert Walker
Robert Walker
17,146 Points
Robert Walker
Robert Walker
17,146 Points

It didn't work but was closer than I have got all day, it returned only three results from what should of been 80 and the first messages was from 2015 then the next two where correctly 2016 but that was the end of the result set too.