Databases
StackOverflow2013
Postsdbo
POSelecting distinct records that has last creation date in similar records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting distinct records that has last creation date in similar records
    primarykey
    data
    text
    <p>I have a table: <code>messages</code> with this structure:</p> <pre><code>`ID` // auto increment `SenderUserID` // foreign key: `ID` from `User` table `ReceiverUserID` // foreign key: `ID` from `User` table `Message` `DateCreated` // timestamp </code></pre> <p>I need to get distinct ReceiverUserIDs from table by this conditions: </p> <ol> <li>Sender user should have conversations with receiver users.</li> <li>Selected distinct <code>ReceiverUserID</code> should give us last <code>DateCreated</code> message.</li> </ol> <p>So in the results, I'll have user last conversations ordered by creation date DESC.</p> <p>When I distinct in select query, mysql return a first record, but I need maximum <code>DateCreated</code> for selected `ReceiverUserID to get last message in top of results.</p> <pre><code>SELECT DISTINCT `m`.`ReceiverUserID` AS `ID` FROM `messages` `m` WHERE (`m`.`SenderUserID` = :UserID OR `m`.`ReceiverUserID` = :UserID) ORDER BY `m`.`DateCreated` DESC //:UserID means LogedInUserID </code></pre> <p><strong>It is good to know It will be exactly like facebook messages board.</strong></p> <hr> <pre><code>ID, SenderUserID, ReceiverUserID, Message, DateCreated 1, 12, 11, 'Hi admin', 2012-10-24 11:07:00 2, 11, 12, 'Hi guy', 2012-10-24 11:08:00 3, 11, 13, 'Hello dear customer', 2012-10-24 11:12:00 4, 11, 13, 'Are you there?', 2012-10-24 11:13:00 5, 13, 11, 'Hiiii', 2012-10-24 11:14:00 // last conversation betwen 11 &amp; 13 </code></pre> <p>Current logedin user id: 11</p> <pre><code>SELECT `m`.`ReceiverUserID` AS `ID`, MAX(`m`.`DateCreated`) as `lastmsg` FROM `messages` `m` WHERE (`m`.`SenderUserID` = 11 OR `m`.`ReceiverUserID` = 11) GROUP BY `m`.`ReceiverUserID` </code></pre> <p>We have problem in this section: It is not return our last conversation between 11 and 13 when receiver user answered the sender message, It will be not seen in results</p> <p>Results:</p> <pre><code>ID, lastmsg 12, 2012-10-24 11:08:00 13, 2012-10-24 11:13:00 // Should be: 13, 2012-10-24 11:14:00 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShallaji
    1. UShallaji
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelecting distinct records that has last creation date in similar records
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSelecting distinct records that has last creation date in similar records
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSelecting distinct records that has last creation date in similar records
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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