CODEWITHSUNDEEP
mysql
daza166
daza166

Reputation: 3693

how to write this specific MySQL query?

I have been trying to create a MySQL query, for my messaging thread (i.e. sender/recipient).

I have a table with a field (msg_id = unique 6 digit id like 123456). This (msg_id) field may have lots of rows (for each msg reply) where each row would have the same (msg_id) like a thread. So, lets say bob started messaging fred thus the thread in table would be something like (crude example just to illustrate point):-

+---------+---------------------------+--------------------------+------------------------+------------+----------+
| msg_id  | sender_email              | recipient_email          | sender_msg             | cur_date   | cur_time |
+---------+---------------------------+--------------------------+------------------------+------------+----------+
| 3189855 | [email protected]           | [email protected]           | hi fred, how are you?  | 2013-03-10 | 17:12:18 |
| 3189855 | [email protected]            | [email protected]          | hi bob, I am great     | 2013-03-10 | 17:15:20 |
| 3189855 | [email protected]           | [email protected]           | hi fred, good to hear  | 2013-03-10 | 17:20:10
| 6749672 | [email protected]          | [email protected]           | some other message     | 2013-03-10 | 14:52:49 |
+---------+---------------------------+--------------------------+------------------------+------------+----------+

When displaying either bobs or freds email inbox's I want to display each unique (msg_id) by latest (cur_date, cur_time) so the newest msg/msg reply goes top of inbox list.

So, lets focus on Freds mailbox as Fred has most messages. I want my query to display the following result when outputting inbox emails:

+---------+---------------------------+--------------------------+------------------------+------------+----------+
| msg_id  | sender_email              | recipient_email          | sender_msg             | cur_date   | cur_time |
+---------+---------------------------+--------------------------+------------------------+------------+----------+
| 3189855 | [email protected]           | [email protected]           | hi fred, good to hear  | 2013-03-10 | 17:20:10 |
| 6749672 | [email protected]          | [email protected]           | some other message     | 2013-03-10 | 14:52:49 |
+---------+---------------------------+--------------------------+------------------------+------------+----------+

So, the query gets each unique (msg_id) and for each unique (msg_id) it gets the last row (i.e. being the last msg in thread sent which works out to the latest msg sent). If that makes sense. My question is therefore how would I generate this SQL query to give above result. My idea is below but does not work:-

SELECT * FROM inbox_table WHERE (recipient_email='[email protected]' AND ORDER BY cur_date DESC, cur_time DESC) GROUP BY msg_id ORDER BY cur_date DESC, cur_time DESC;

Any ideas would be greatly appreciated and I hope I explained this well.

Upvotes: 0

Views: 66

Answers (2)

Bart Friederichs
Bart Friederichs

Reputation: 33511

Probably, you just need to add quotes around the strings, and the ORDER BY clause needs to be in the end, and only one time:

SELECT * FROM inbox_table WHERE 
   recipient_email='[email protected]' 
   GROUP BY msg_id ORDER BY cur_date DESC, cur_time DESC;

It might make sense to add an aggregate function to the SELECT clause, for example COUNT, so you also know how many messages are in the thread:

SELECT *,COUNT(msg_id) FROM inbox_table WHERE 
   recipient_email='[email protected]' 
   GROUP BY msg_id ORDER BY cur_date DESC, cur_time DESC;

Finally, I'd advise to change the name msg_id into thread_id, as it covers the meaning a lot better.

If you want the newest record by time, use this:

 SELECT *,MAX(CONCAT(cur_date,' ',cur_time)) FROM inbox_table WHERE 
   recipient_email='[email protected]' 
   GROUP BY msg_id

Upvotes: 1

hd1
hd1

Reputation: 34657

Check this fiddle, basically, you didn't quote your string and the parentheses in WHERE are unnecessary.

Upvotes: 0

Related Questions