CODEWITHSUNDEEP
phpmysqldatabase
Jadzia
Jadzia

Reputation: 164

Private chat system MYSQL query ORDERBY and GROUPBY

I am creating private message system in php/mysql.

I created message tables: 

messages(message_id|sender_id|receiver_id|message_text|created_time)

Here is data in table

message_id|sender_id||receiver_id|message_text|   created_time
   1         101          102       Message A    2012-06-07 08:07:18
   2         101          102       Message B    2012-06-07 08:10:20
   3         103          102       Message C    2012-06-07 08:12:43

The output I want is:

     sender_id|message_text |   created_time
       101      Message B     2012-06-07 08:10:20
       103      Message C     2012-06-07 08:12:43

Something like:

SELECT sender_id,message_text,created_time from messages
WHERE receiver_id='102'
GROUP BY sender_id
ORDERBY BY created_time

I want to show the last message of any user.

Upvotes: 0

Views: 1466

Answers (3)

Darshana
Darshana

Reputation: 2548

SELECT sender_id, MESSAGE_TEXT, created_time
FROM messages
WHERE receiver_id = 102
  AND created_time IN
    (SELECT MAX(created_time)
     FROM messages
     GROUP BY sender_id);

Demo

Upvotes: 2

Arion
Arion

Reputation: 31249

Maybe something like this:

SELECT
    messages.sender_id,
    messages.message_text,
    messages.created_time
FROM
    messages
    JOIN
        (
            SELECT
                MAX(created_time) AS LatestCreated,
                t.sender_id
            FROM
                messages AS t
            GROUP BY
                t.sender_id
        ) AS Latest
        ON Latest.sender_id=messages.sender_id
        AND Latest.LatestCreated=messages.created_time
WHERE
    messages.receiver_id=102
ORDER BY
    messages.created_time

Demo

Upvotes: 3

James Healey
James Healey

Reputation: 464

You were close - you typed 'ORDER BY' as 'ORDERBY BY'. You'll get the correct data with the WHERE clause you already have. You also need to order your date in descending order to show the newest first (as below).

I can see that you're asking to aggregate the data in some way, but I don't quite understand why as each row's unique.

Solution

SELECT sender_id, message_text, created_time FROM messages
WHERE receiver_id = 102
ORDER BY created_time DESC

Upvotes: -1

Related Questions