PHP MySQL SELECT newest entry grouped by an ID and ordered by timestamp

Question

I am working on an problem regarding Selecting data from two MySQL tables.

First table holds messages | messages | (id, msg_group_id, to_user_id, from_user_id, datetime)

Second table holds user data | profiles | (user_id, name, firstname, ...)

ATM it works the way, that I can select ALL messages with a certain 'to_id' and by adding a JOIN statement getting the name and firstname of the user who sends the message.

My problem now is that I can not figure out a way to ONLY select the newest message of a certain msg_group_id.

I already tried GROUP BY msg_group_id combined with ORDER BY datetime DESC.

But that only throws the very first entry in message table. But I want to last one. :-)

I hope you can help me. :-)

My actual SQL statement:

SELECT LEFT(messages.message, 10) AS message, 
`messages`.`msg_group_id`,  
`messages`.`datetime`,  
`profiles`.`name`,  
`profiles`.`firstname`  
FROM `messages`  
LEFT JOIN `profiles`  
ON `messages`.`from_user_id` = `profiles`.`user_id`  
WHERE `to_user_id` = '2'  
ORDER BY `datetime` DESC  
LIMIT 20;

Thanks in Advance

Sample INPUT:

[messages]

|id|msg_group_id|to_user_is|from_user_id|message       |datetime|
0  | 1          | 1        | 2          | Hello World1 | 2015-12-21 10:42:00
1  | 1          | 1        | 2          | Hello World2 | 2015-12-21 10:43:00
2  | 1          | 1        | 2          | Hello World3 | 2015-12-21 10:44:00

[profiles]

user_id|name    |firstname|
1      | Test   | User
2      | Thanks | Worldname

Result (what I don't want)

message|msg_group_id|datetime|name|firstname
Hello World1 | 1 | 2015-12-21 10:42:00 | Thanks | Worldname

Result (what I want)

message|msg_group_id|datetime|name|firstname
Hello World3 | 1 | 2015-12-21 10:44:00 | Thanks | Worldname

just added some...but i can not find a way to format it in an appropriate way do I have to mark it as code? --- ha at least the formatting works (in a way) ;-) — nira, Dec 21 '15 at 09:48

Pathik Vejani · Answer 1 · 2015-12-21T10:17:22.653

0

May be this query can help:

SELECT m.message, m.msg_group_id, m.datetime, u.name, u.firstname 
FROM message as m, profiles as u
WHERE m.from_user_id = u.user_id 
GROUP BY m.msg_group_id
ORDER BY m.datetime DESC

Or use INNER JOIN

SELECT m.message, m.msg_group_id, m.datetime, u.name, u.firstname 
FROM message as m
INNER JOIN profiles as u ON m.from_user_id = u.user_id
GROUP BY m.msg_group_id
ORDER BY m.datetime DESC

edited Dec 21 '15 at 10:17

answered Dec 21 '15 at 09:54

Pathik Vejani

4,263
8
57
98

if you want newest record then just use `ORDER BY m.datetime DESC` – Pathik Vejani Dec 21 '15 at 09:58
Hi, first of all thank you very much. But it doesn't do what I expect. Expecting one user receives more than 1 message this will not work. I guess. – nira Dec 21 '15 at 10:01
it should give output as you have mentioned – Pathik Vejani Dec 21 '15 at 10:01
@nira can you please check once my updated answer? try my first query – Pathik Vejani Dec 21 '15 at 10:03
yes I tried it again. It only delivers 1 result as the LIMIT 1 says. But the user already has more than 1 message with different msg_group_id's – nira Dec 21 '15 at 10:08
I also did that :-) But then it shows all messages :-) – nira Dec 21 '15 at 10:11
yes it'll show all message bcoz that user_id condition meets the criteria – Pathik Vejani Dec 21 '15 at 10:12
What I want is: - Get for every msg_group_id only the very newest msg - Get to this message the name&firstname of the sender (from_user) – nira Dec 21 '15 at 10:14
@nira i added group by condition.. see whether it can help – Pathik Vejani Dec 21 '15 at 10:17
yes I already tried this as well...that delivers the same as my (crazy left join statement in the question) it shows the very oldest message in the table. I am confused about it as we set it DESC. – nira Dec 21 '15 at 10:23
it should descending the order by date – Pathik Vejani Dec 21 '15 at 10:51

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

I guess I solved the Problem with the help of another thread:

https://stackoverflow.com/a/1313140/4493030

My SQL Statement as follows:

SELECT `messages`.*, `profiles`.`nick_name` 
FROM `messages`  
LEFT JOIN `profiles`  
ON `messages`.`from_user_id` = `profiles`.`user_id` 
INNER JOIN 
(SELECT konversation_id, MAX(id) AS maxid FROM messages
WHERE messages.to_user_id = 2 
GROUP BY konversation_id) AS b
ON messages.id = b.maxid
WHERE `to_user_id` = '2'  
ORDER BY `datetime` DESC  
LIMIT 20;

Thanks to all of you who tried to help.

I found a way to tight it down

SELECT messages.to_user_id, messages.msg_group_id, MAX(messages.id) AS maxid, messages.from_user_id, profiles.name 
FROM messages 
LEFT JOIN profiles 
ON messages.from_user_id = profiles.user_id 
WHERE messages.to_user_id = 2 
GROUP BY msg_group_id

PHP MySQL SELECT newest entry grouped by an ID and ordered by timestamp

2 Answers2