Jump to content

Refining SELECT statement to include one record/group based on date


Dan06

Recommended Posts

I'm using a query that selects all records that have a particular recipient and matching ids. I want to refine the select criteria to include one record from each group based on recipient, message id, and latest date. The records are grouped based on "threadId." How can I do this?

 

There are two tables involved: message_inbox and message_content.

 

The message_content table has the following fields:

msgId, threadId, msgParent, msgChild, msgDate, msgSubject, msgContent

 

The message_inbox table has the following fields:

msgRecipient, msgSender, msgId

 

Here is my current mysql query:

$messagesQuery = sprintf("SELECT msgSender, message_content.msgId, msgDate, msgSubject, msgContent FROM utilist.message_inbox, utilist.message_content WHERE msgRecipient = %s AND message_inbox.msgId = message_content.msgId",
$format->formatValue($_SESSION["userId"]));

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.