'Select the recent record of each person [duplicate]

I have the users data in mysql table like below

Index_No Message_From Message_To Message Message_Time(Current TimeStamp)
01 [email protected] [email protected] hello 009 2022-04-24 12:26:13
02 [email protected] [email protected] hi 567 2022-04-24 12:27:13
03 [email protected] [email protected] how are you 2022-04-24 12:28:13
04 [email protected] [email protected] How is the weather 2022-04-24 12:29:13

I need to show the latest record of each user like

Index_No Message_From Message_To Message Message_Time(Current TimeStamp)
03 [email protected] [email protected] how are you 2022-04-24 12:28:13
04 [email protected] [email protected] How is the weather 2022-04-24 12:29:13

I am using MySQL does any one know how i will achieve the required result?

mysql


Solution 1:[1]

you can use row_number() OVER (partition by Message_From order by Message_Time(Current TimeStamp) desc as rank) then you get the rank=1

Solution 2:[2]

SELECT
    Index_No, Message_From, Message_To, Message, Message_Time 
FROM `table` 
GROUP BY Message_To 
ORDER BY Message_Time DESC

Solution 3:[3]

If you are using older versions of mysql then You need to pick the latest time in your where clause:

SELECT
Index_No, Message_From, Message_To, Message, Message_Time 
FROM `table` WHERE Message_Time=(SELECT MAX(Message_Time) FROM `table` t1
 WHERE t1.Message_To=Message_To LIMIT 1) `GROUP BY Message_To `

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Leal Li
Solution 2 user973254
Solution 3 Asgar

Related Questions