lundi 27 juin 2016

MYSQL Query Select which has same column and group by

I have database table named "messages". I want to get result total message per user and the last message from them. Below is my structure table and result. Here is my SQL:

SELECT COUNT(email), messages.*
FROM messages
WHERE
email = message_id
group by email
order by created_at desc

Here is my Table Structure

+---------------------+----------------+---------------------+---------------------+
| email               | message        | message_id          | created_at          |
+---------------------+----------------+---------------------+---------------------+
| memberONE@gmail.com | first message  | memberONE@gmail.com | 2016-06-26 10:00:00 |
+---------------------+----------------+---------------------+---------------------+
| Admin               | reply admin    | memberONE@gmail.com | 2016-06-26 12:00:00 |
+---------------------+----------------+---------------------+---------------------+
| memberONE@gmail.com | last message   | memberONE@gmail.com | 2016-06-26 22:00:00 |
+---------------------+----------------+---------------------+---------------------+
| memberTWO@gmail.com | first message  | memberTWO@gmail.com | 2016-06-26 10:00:00 |
+---------------------+----------------+---------------------+---------------------+
| memberTWO@gmail.com | second message | memberTWO@gmail.com | 2016-06-26 12:00:00 |
+---------------------+----------------+---------------------+---------------------+
| Admin               | reply admin    | memberTWO@gmail.com | 2016-06-26 18:00:00 |
+---------------------+----------------+---------------------+---------------------+
| memberTWO@gmail.com | last message   | memberTWO@gmail.com | 2016-06-26 22:00:00 |
+---------------------+----------------+---------------------+---------------------+

Result from Above code is this :

+--------------+---------------------+---------------+---------------------+---------------------+
| COUNT(email) | email               | message       | message_id          | created_at          |
+--------------+---------------------+---------------+---------------------+---------------------+
| 2            | memberONE@gmail.com | first message | memberONE@gmail.com | 2016-06-26 10:00:00 |
+--------------+---------------------+---------------+---------------------+---------------------+
| 3            | memberTWO@gmail.com | first message | memberTWO@gmail.com | 2016-06-26 10:00:00 |
+--------------+---------------------+---------------+---------------------+---------------------+

Expected Result : Just want to get the last message from user where column created_at is the last

+--------------+---------------------+--------------+---------------------+---------------------+
| COUNT(email) | email               | message      | message_id          | created_at          |
+--------------+---------------------+--------------+---------------------+---------------------+
| 2            | memberONE@gmail.com | last message | memberONE@gmail.com | 2016-06-26 22:00:00 |
+--------------+---------------------+--------------+---------------------+---------------------+
| 3            | memberTWO@gmail.com | last message | memberTWO@gmail.com | 2016-06-26 22:00:00 |
+--------------+---------------------+--------------+---------------------+---------------------+

Please help me. thank you so much

Aucun commentaire:

Enregistrer un commentaire