-1

I have the users data in mysql table like below

Index_No Message_From Message_To Message Message_Time(Current TimeStamp)
01 123@mail.com 009@mail.com hello 009 2022-04-24 12:26:13
02 567@mail.com 008@mail.com hi 567 2022-04-24 12:27:13
03 123@mail.com 009@mail.com how are you 2022-04-24 12:28:13
04 567@mail.com 008@mail.com 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 123@mail.com 009@mail.com how are you 2022-04-24 12:28:13
04 567@mail.com 008@mail.com 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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Adeelzaman786
  • 7
  • 6

2 Answers2

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

Leal Li
  • 247
  • 1
  • 2
  • 10
-1

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 `
Asgar
  • 1,920
  • 2
  • 8
  • 17
  • This query is showing me only the only the Index_No 4 data – Adeelzaman786 Apr 24 '22 at 14:38
  • 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_From=Message_From LIMIT 1) `GROUP BY Message_From ` – Asgar Apr 24 '22 at 14:42
  • its showing only Index_No 4 data not the Index_No 3 data i need to show the latest data of all the persons – Adeelzaman786 Apr 24 '22 at 14:52
  • Brother just add an alias in your primary query SELECT Index_No, Message_From, Message_To, Message, Message_Time FROM `table` t WHERE Message_Time=(SELECT MAX(Message_Time) FROM `table` t1 WHERE t1.Message_From=t.Message_From LIMIT 1) GROUP BY Message_From – Asgar Apr 24 '22 at 15:10
  • still its not working and showing only the Index_No 4 data – Adeelzaman786 Apr 24 '22 at 15:19
  • I am not a php developer so I can't tell if your code is written in a way that it can hold list but I am pretty sure the query I gave you is fine and will give you the desired result set. – Asgar Apr 25 '22 at 09:41