Sarath Jasrin Sarath Jasrin - 2 years ago 175
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.



Example Data:

Id email msg
1 This is test
2 All is well
3 This is test2

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

Id email msg

2 All is well
3 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

Guide me if I wrong

Answer Source

Use This query:

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

This will join with the same table and with condition < , 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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download