I'm trying to select the one row per user ID for a conversation list. I'm trying to show users a list of their active conversations (similar functionality can be shown on Facebook's messages page, it shows all of your conversations with the profile name and the latest message, then you click on them to see the entire chat history), but only displaying the last message from each user they're chatting with. Here's the query I tried using so far to select a single message from each user they're chatting with, but it's showing the oldest message. I tried changing the ORDER by it didn't help, so any help offered is appreciated:
SELECT `conversations`.`from_id`,
`profile`.`username`,
`conversations`.`date`,
`conversations`.`body`
FROM
`website`.`conversations`,
`website`.`profile`
WHERE
`conversations`.`to_id` = ?
AND `profile`.`profile_id` = `conversations`.`from_id`
GROUP BY `profile`.`user_id`
ORDER BY `conversations`.`id` DESC
LIMIT 15
I'm ordering by the conversation id (auto incrementing ID), so I was trying to get the highest ID (which would be the newest record) to be returned but I'm doing something wrong.
Update: Here's the table structure:
CREATE TABLE `conversations` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`to_id` int(10) unsigned NOT NULL,
`from_id` int(10) unsigned NOT NULL,
`body` mediumtext NOT NULL,
`date` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `to_id` (`to_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I've tried multiple queries from other "similar" questions (obviously editing them to make them reflect my table), none of which worked.
Update again: I tried the following query but now it is returning just one single row, and I'm hoping to return a maximum of 15 (LIMIT 15 in my previous query) unique conversations:
SELECT p1.id, p1.body, profile.username
FROM profile, conversations p1 LEFT JOIN conversations p2
ON (p1.to_id = p2.to_id AND p1.id < p2.id)
WHERE p2.id IS NULL AND p1.to_id = *INSERT_USER_ID* AND profile.user_id=p1.from;
