CODEWITHSUNDEEP
mysql
Lunar
Lunar

Reputation: 4711

MySQL sort grouped data

I have tried to program a inbox that display messages in the order they were received and then by if they have been read or not, it seemed to work for a while, but not it doesn't. It may have only worked under certain circumstances maybe..

Anyway here is my query;

SELECT `id`, `from_userid`, `read`, max(sent) AS sent 
FROM (`who_messages`) 
WHERE `to_userid` = '41' 
GROUP BY `from_userid` 
ORDER BY `read` ASC, `sent` DESC

I believe the problem is that the messages are being grouped in the wrong order.. as the inbox is always showing as read, when new messages exist. I get the right time of the new messages, but I am guessing this because I selected max(sent).

Is my logic wrong? or can I sort and then group as all my efforts have resulted in 'Every derived table must have its own alias'

Upvotes: 1

Views: 387

Answers (4)

Olivier Coilland
Olivier Coilland

Reputation: 3096

As Quassnoi said, you are using a GROUP BY query and ordering on 'read' which is not an aggregate function. Therefore you can't be certain of the value used by the MySQL engine (usually the last of the group but...)

I would suggest writing your query this way, as it doesn't involve any subquery and has some many other performance-friendly usage:

SELECT
    from_userid,
    COUNT(*) AS nb_messages,
    SUM(NOT is_read) AS nb_unread_messages,
    MAX(sent) AS last_sent
FROM who_messages
WHERE to_userid = 41
GROUP BY from_userid
ORDER BY nb_unread_messages DESC, last_sent DESC;

(I used Andy Jones' fiddle schema: http://sqlfiddle.com/#!2/4f63d/8.
By the way, many thanks Andy, this site is great !)

Hope this help !

Upvotes: 2

Nikola Bogdanović
Nikola Bogdanović

Reputation: 3213

"inbox that display messages in the order they were received and then by if they have been read or not ... however it is suppose to be the latest message" - assumes read is a nullable date/time column, and messages are stored in the order they are sent (newer have larger id than older - autoid)

SELECT wm.id, wm.from_userid, (wm.read IS NULL) as unread, wm.sent
FROM (SELECT MAX(id) AS id FROM who_messages WHERE to_userid = '41' GROUP BY from_userid) sub
INNER JOIN who_messages wm ON sub.id = wm.id
ORDER BY wm.sent DESC, wm.read

Upvotes: 1

Andy Jones
Andy Jones

Reputation: 6275

Setup an SQL Fiddle - here's the best I came up with. Basically I do the ordering first in a sub-query then group them afterwards. That seemed to work with the (limited) test data I entered. 

SELECT *
FROM (SELECT id, from_userid, is_read, sent
      FROM who_messages 
      WHERE to_userid = 41
      ORDER BY from_userid ASC, is_read ASC) m 
GROUP BY m.from_userid
ORDER BY m.is_read ASC, m.sent DESC

See the fiddle to play around: http://sqlfiddle.com/#!2/4f63d/8

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425843

You are selecting non-grouping fields in a grouped query. It is not guaranteed which record of the group will be returned, and ORDER BY is processed after GROUP BY.

Try this:

SELECT  m.*
FROM    (
        SELECT  DISTINCT from_userid
        FROM    who_messages
        WHERE   to_userid = 41
        ) md
JOIN    who_messages m
ON      m.id = 
        (
        SELECT  mi.id
        FROM    who_message mi
        WHERE   (mi.to_userid, mi.from_userid) = (41, md.from_userid)
        ORDER BY
                mi.sent DESC, mi.id DESC
        LIMIT 1
        )

Create an index on who_message (to_userid, from_userid, sent, id) for this to work fast.

Update

The above query will return the record for the last message from any given user (including its read status). If you want to check that you have any unread messages from the user, use this:

SELECT  m.*, md.all_read
FROM    (
        SELECT  from_userid, MIN(read) AS all_read
        FROM    who_messages
        WHERE   to_userid = 41
        GROUP BY
                from_userid
        ) md
JOIN    who_messages m
ON      m.id = 
        (
        SELECT  mi.id
        FROM    who_message mi
        WHERE   (mi.to_userid, mi.from_userid) = (41, md.from_userid)
        ORDER BY
                mi.sent DESC, mi.id DESC
        LIMIT 1
        )

For this to work fast, create an index on who_message (to_userid, from_userid, read) (in addition to the previous index).

Upvotes: 2

Related Questions