Private chat system MYSQL query ORDERBY and GROUPBY

Question

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.

@EdouardMoinard, in my question its written "The output I want is:" — Jadzia, Jun 07 '12 at 09:50
Oh sorry I missread your db I thought your query was good... My bad ^^ — Edouard Moinard, Jun 07 '12 at 09:52
Please check @Arion answer. Its working. I have added link as Demo there... — Fahim Parkar, Jun 07 '12 at 10:07

score 3 · Answer 1 · edited Jun 07 '12 at 10:06

3

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

edited Jun 07 '12 at 10:06

Fahim Parkar

30,974
45
160
276

answered Jun 07 '12 at 09:50

Arion

31,011
10
70
88

I think your answer is quite near to solution. But the problem I am facing with you answer is: if there is 2 messages from a same sender then it works ok but if 3 then not grouping sender_id. – Jadzia Jun 07 '12 at 10:06
1

@With three answer it is also working. See [here](http://sqlfiddle.com/#!2/b5ce0/1) Else can you edit the sqlfiddle in input and provide the link?? – Fahim Parkar Jun 07 '12 at 10:10
Oo fahim, thanks for this. Really appreciate. yes it is working correctly. where I made mistake is I copy data of already existed row and dint changed time. The only problem is coming if created_time have same time value. – Jadzia Jun 07 '12 at 10:18
:) this mistake happen very often :) :D – Fahim Parkar Jun 07 '12 at 10:20

score 2 · Accepted Answer · edited Jun 07 '12 at 10:22

2

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

edited Jun 07 '12 at 10:22

Fahim Parkar

30,974
45
160
276

answered Jun 07 '12 at 10:16

Darshana

2,462
6
28
54

I extended my question if you can help http://stackoverflow.com/questions/10941763/private-chat-system-mysql-query-to-display-last-message-of-sender-receiver – Jadzia Jun 08 '12 at 00:54

score -1 · Answer 3 · answered Jun 07 '12 at 09:49

-1

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

answered Jun 07 '12 at 09:49

James Healey

464
4
4

Thanks james for you answer. But your query will give me all 3 rows in table with orderby time. I am looking for groupby as well. – Jadzia Jun 07 '12 at 09:52
So - you only want to show the last message per sender? Your intention is to hide previously sent messages? – James Healey Jun 07 '12 at 09:54
Yes right as explained in my question. I want only latest message received. – Jadzia Jun 07 '12 at 09:56

Private chat system MYSQL query ORDERBY and GROUPBY

3 Answers3

Demo

Demo

Solution

Linked