Let's say I have a table called messages
with the columns:
id | from_id | to_id | subject | message | timestamp
I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.
This query seems to get me close to the result I need:
SELECT * FROM messages GROUP BY from_id
However the query is giving me the oldest message from each user and not the newest.
I can't figure this one out.
This question is related to
mysql
sql
greatest-n-per-group
You need to order them.
SELECT * FROM messages GROUP BY from_id ORDER BY timestamp DESC LIMIT 1
Just complementing what Devart said, the below code is not ordering according to the question:
SELECT t1.* FROM messages t1
JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;
The "GROUP BY" clause must be in the main query since that we need first reorder the "SOURCE" to get the needed "grouping" so:
SELECT t1.* FROM messages t1
JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages ORDER BY timestamp DESC) t2
ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp GROUP BY t2.timestamp;
Regards,
this query return last record for every Form_id:
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.Form_id = m2.Form_id AND m1.id < m2.id)
WHERE m2.id IS NULL;
Try this
SELECT * FROM messages where id in (SELECT max(id) FROM messages GROUP BY from_id ) order by id desc
This is a standard problem.
Note that MySQL allows you to omit columns from the GROUP BY clause, which Standard SQL does not, but you do not get deterministic results in general when you use the MySQL facility.
SELECT *
FROM Messages AS M
JOIN (SELECT To_ID, From_ID, MAX(TimeStamp) AS Most_Recent
FROM Messages
WHERE To_ID = 12345678
GROUP BY From_ID
) AS R
ON R.To_ID = M.To_ID AND R.From_ID = M.From_ID AND R.Most_Recent = M.TimeStamp
WHERE M.To_ID = 12345678
I've added a filter on the To_ID
to match what you're likely to have. The query will work without it, but will return a lot more data in general. The condition should not need to be stated in both the nested query and the outer query (the optimizer should push the condition down automatically), but it can do no harm to repeat the condition as shown.
Source: Stackoverflow.com