CODEWITHSUNDEEP
mysqlsqlcakephpcakephp-1.3greatest-n-per-group
Harsha M V
Harsha M V

Reputation: 54949

MySQL query to pull only the latest record

I am creating a simple chat module where there are two levels.

  1. Inbox
  2. Conversation

in the Inbox i want to list the conversation between me and other users and display the latest chat message from that particular user.

messages(id, user_id, receiver_id, message, timestamp)

Dummy data

1, 1, 2, "hi there", today

2, 1, 4, "Hey Tommy", yesterday

3, 1, 2, "Wanna meet tonite?", now

Now when i display the list i want to just display two messages one with user id - 4 and user id 2. But the latest message of userid 2 should be displayed only.

How to do a query where i can get only the latest row from a particular user ?

Upvotes: 1

Views: 2158

Answers (4)

Simon
Simon

Reputation: 9425

This is beleive is an efficient way to acheive this:

SELECT M1.* FROM Messages M1 LEFT JOIN Messages M2 
ON M1.ReceiverID = M2.ReceiverID AND M1.Timestamp < M2.Timestamp
WHERE M2.ID IS NULL

Upvotes: 1

Nick Zinger
Nick Zinger

Reputation: 1174

This might work:

$latest_per_user_id = $this->Message->find('all', array(
    'fields'=>'DISTINCT user_id',
    'order' => 'timestamp DESC'
));

EDIT: If you just need info for one user you can do this: 

$latest_msg_by_user = $this->Message->find('first', array(
        'conditions'=>array('user_id'=>$user_id),
        'order' => 'timestamp DESC'
    ));

You could put this in a loop to get it for each user.

Upvotes: 1

Henri
Henri

Reputation: 740

I would try it like this:

$user_id = 2;

$latestmessage = $this->Message->find('first', array(
    'conditions' => array(
        'user_id' => $user_id
    ),
    'order' => 'id DESC'
));

Upvotes: 1

Abhay
Abhay

Reputation: 6645

SELECT *
FROM (SELECT *
FROM `messages`
WHERE `user_id` = 1
ORDER BY `timestamp` DESC) TT
GROUP BY TT.`receiver_id`;

Hope the above helps!

Upvotes: 2

Related Questions