Sarath Jasrin Sarath Jasrin - 6 months ago 36
SQL Question

Select max value for a VARCHAR column - MySQL

My table for message inbox concept email is a primary in the table. I want to get the last message which is sent by particular email.

tbl_msg:

id
email
msg


Example Data:


Id email msg
1 xyz@gmail.com This is test
2 abc@gmail.com All is well
3 xyz@gmail.com This is test2


I want to get the last appearance of each email and msg

Id email msg

2 abc@gmail.com All is well
3 xyz@gmail.com This is test2


What I tried:

SELECT cnote.`id`,cnote.`email`,cnote.`msg` FROM `tbl_msg` cnote inner join (select distinct email,id from client_com group by email) as note
on cnote.id=note.id


Guide me if I wrong

Answer

Use This query:

SELECT m1.* FROM tbl_msgs m1 LEFT JOIN tbl_msgs m2  ON (m1.email = m2.email AND m1.id < m2.id) WHERE m2.id IS NULL;

This will join with the same table and with condition m1.id < m2.id , will get the latest one.

Another option is using subquery:

SELECT * FROM tbl_msgs WHERE id IN (SELECT MAX(id) FROM tbl_msgs GROUP BY email);
Comments