CODEWITHSUNDEEP
phpmysql
Tahola
Tahola

Reputation: 1015

Problem with group by sql

here is my problem, i would like to make "twitter like" direct message system (inbox group by sender), my database look like this:

CREATE TABLE `messages` 
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` int(11) NOT NULL,
  `receiver` int(11) NOT NULL,
  `text` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

So i try something like this:

$requette = mysql_query("SELECT * FROM messages WHERE receiver = '$user_id' 
GROUP BY sender ORDER BY date DESC LIMIT 0,10");

while($data = mysql_fetch_array($requette)) 
{
  echo $data[date].'<br/>'.$data[text].'<hr>';
}

Now discussion is grouped by sender who send the last, this is good.

But my problem is : $data[date] and $data[text], they are from the first ID and i want to get it from the last so i can preview the text and the time of the last message.

Thank for your help and sorry for my english (I'm French).

Upvotes: 0

Views: 228

Answers (3)

rsenna
rsenna

Reputation: 11964

I guess this will do the job:

$requette = mysql_query("SELECT * FROM messages WHERE id IN
(SELECT MAX(id) FROM messages WHERE receiver = '$user_id' GROUP BY sender)
ORDER BY date DESC LIMIT 0,10");

while($data = mysql_fetch_array($requette)) 
{
    echo $data[date].'<br/>'.$data[text].'<hr>';
}

Edit Just a brief explanation: the inner query is responsible for taking all the last messages from each user, based upon the ID field (I'm considering that the last messages have the highest ID value, which is most probably true).

Upvotes: 2

dqhendricks
dqhendricks

Reputation: 19251

$requette = mysql_query("SELECT * FROM messages WHERE receiver = '$user_id' AND date = (SELECT MAX(messages2.date) FROM messages AS messages2 WHERE messages.sender = messages2.sender LIMIT 1) GROUP BY sender ORDER BY date DESC LIMIT 0,10");

selects only records that have the max date for each sender.

Upvotes: 0

Will Charczuk
Will Charczuk

Reputation: 919

Ok, this might not help you with MYSQL, but for anyone using T-SQL (MSSQL Server) you can use the ROW_NUMBER() operation (and over partition/order) to get an order and an index, and then use an outer select to get the element with the first of these indexes.

Example (uses syntax from stack exchange query tester):

select * from
(
    select
        postTypeId
        , id
        , ROW_NUMBER() over (partition by postTypeid order by id) as orderIndex
    from posts
​) as a
where a.orderIndex = 1​

You may be able to use a similar strategy in MYSQL.

Upvotes: 0

Related Questions