CODEWITHSUNDEEP
phpmysql
Sarath Kumar
Sarath Kumar

Reputation: 1146

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   [email protected]    This is test    
2   [email protected]    All is well
3   [email protected]    This is test2

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

Id  email            msg

2   [email protected]    All is well
3   [email protected]    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

Upvotes: 0

Views: 303

Answers (5)

Jay Blanchard
Jay Blanchard

Reputation: 34426

To get the latest messsage for each email address in the table you do not need a JOIN, you just need to ORDER and GROUP BY:

SELECT `id`, `email`, `msg`
FROM `tbl_msg`
GROUP BY `email`
ORDER BY `id` DESC

Upvotes: 1

BIDeveloper
BIDeveloper

Reputation: 2638

SELECT ID, email, Msg FROM tbl_msg WHERE ID IN ( (SELECT MAX(ID) FROM tbl_msg GROUP BY email))

Upvotes: 0

Jack Hales
Jack Hales

Reputation: 1654

If you are running a PDO connection (The way I know to do it, not sure if there is a MySQLi way)

$theLastIdGiven = $handlerDbConnection->lastInsertId();

Or if you want to run it through a query, and get the last result, just add ORDER BY id DESC LIMIT 0, 1 and that will order the data by id in a descending form then get the first bit of data :)

Hope this helped you!

Upvotes: 0

Grommy
Grommy

Reputation: 367

You can use this query:

SELECT id, email, msg FROM you_table 
GROUP BY email
ORDER BY id DESC

With this one you will have only 1 row per email, and you'll have the last because is ordered by id DESC.

Upvotes: 0

Aju John
Aju John

Reputation: 2244

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);

Upvotes: 1

Related Questions