CODEWITHSUNDEEP
mysql
Red Virus
Red Virus

Reputation: 1707

Group By gives me the oldest results from the database

I' am currently using the following SQL to get the latest message from the Database. The following SQL gives me the oldest message in the database. I' am Grouping By message_replay_id because this is the common column in the database and I' am also using it for the reference.

Database Structure and Result from the Query

enter image description here

This is all the records without using the Query enter image description here

MySQL

SELECT * FROM messages
WHERE message_from = '1' || message_to = '1'
GROUP BY message_replay_id 
ORDER BY message_id DESC

As you can see, the message_id are

11, 7, 5, 1 (Oldest ID's)

but infact the latest ID's are

13, 7, 6, 20

My Question again is what am I doing wrong that I' am not able to get the Latest Results from the Database.

Upvotes: 0

Views: 50

Answers (1)

Kickstart
Kickstart

Reputation: 21513

To get the latest result you need match up the latest row.

The easiest way is using a sub query to get the latest row for each message_replay_id, and then join that back against messages to get the rest of the details.

SELECT a.*
FROM messages a
INNER JOIN
(
    SELECT message_replay_id, MAX(message_id) AS latest_message
    FROM messages
    WHERE message_from = '1' || message_to = '1'
    GROUP BY message_replay_id 
) b
ON a.message_replay_id = b.message_replay_id
AND a.message_id = b.latest_message
ORDER BY a.message_id DESC

Upvotes: 1

Related Questions