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