StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhow to write this specific MySQL query?
text
Body
copied!<p>I have been trying to create a MySQL query, for my messaging thread (i.e. sender/recipient).</p> <p>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):-</p> <pre><code>+---------+---------------------------+--------------------------+------------------------+------------+----------+ | msg_id | sender_email | recipient_email | sender_msg | cur_date | cur_time | +---------+---------------------------+--------------------------+------------------------+------------+----------+ | 3189855 | bob@hotmail.com | fred@yahoo.com | hi fred, how are you? | 2013-03-10 | 17:12:18 | | 3189855 | fred@yahoo.com | bob@hotmail.com | hi bob, I am great | 2013-03-10 | 17:15:20 | | 3189855 | bob@hotmail.com | fred@yahoo.com | hi fred, good to hear | 2013-03-10 | 17:20:10 | 6749672 | jenny@barclay.cc | fred@yahoo.com | some other message | 2013-03-10 | 14:52:49 | +---------+---------------------------+--------------------------+------------------------+------------+----------+ </code></pre> <p>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. </p> <p>So, lets focus on Freds mailbox as Fred has most messages. I want my query to display the following result when outputting inbox emails:</p> <pre><code>+---------+---------------------------+--------------------------+------------------------+------------+----------+ | msg_id | sender_email | recipient_email | sender_msg | cur_date | cur_time | +---------+---------------------------+--------------------------+------------------------+------------+----------+ | 3189855 | bob@hotmail.com | fred@yahoo.com | hi fred, good to hear | 2013-03-10 | 17:20:10 | | 6749672 | jenny@barclay.cc | fred@yahoo.com | some other message | 2013-03-10 | 14:52:49 | +---------+---------------------------+--------------------------+------------------------+------------+----------+ </code></pre> <p>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:-</p> <pre><code>SELECT * FROM inbox_table WHERE (recipient_email='fred@yahoo.com' AND ORDER BY cur_date DESC, cur_time DESC) GROUP BY msg_id ORDER BY cur_date DESC, cur_time DESC; </code></pre> <p>Any ideas would be greatly appreciated and I hope I explained this well.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload