Am trying to sort users who have more unread messages to appear first in mysql results while including also the ones with read messages appearing as last

Am using mysql 5.6.17 table structure

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| msgID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| userID    | int(11)      | NO   |     | NULL    |                |
| msgText   | varchar(255) | NO   |     | NULL    |                |
| msgStatus | varchar(255) | NO   |     | NULL    |                |
| userName  | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

my data is like this with out sorting

+-------+--------+-------------------+-----------+----------+
| msgID | userID | msgText           | msgStatus | userName |
+-------+--------+-------------------+-----------+----------+
|     1 |      1 | hi                | unread    | Francis  |
|     2 |      2 | hello             | unread    | Emma     |
|     3 |      1 | good              | unread    | Francis  |
|     4 |      1 | your not easy     | read      | Francis  |
|     5 |      2 | just no that      | unread    | Emma     |
|     6 |      2 | don't lose it bro | unread    | Emma     |
|     7 |      2 | good keep it up   | unread    | Emma     |
|     8 |      3 | i don't hate it   | unread    | John     |
+-------+--------+-------------------+-----------+----------+

if i run the group query "select userID, userName FROM msg GROUP BY userID"; it groups the results like this

+--------+----------+
| userID | userName |
+--------+----------+
|      1 | Francis  |
|      2 | Emma     |
|      3 | John     |
+--------+----------+

but i need the ones with more of their "msgStatus" equaling to "unread" to appear on top in order including even those who have "read" on "msgStatus" only, thanks guys in advance

select userID, userName FROM msg GROUP BY userID;

2 Answers

0
Ronald Aaronson On Best Solutions

My approach is to try to compute for each userId and userName an unread count and a read count. Given those 4 computed columns, you simply select distinct userIds giving preference to those rows where unread count is > unread count. The computation would be simplified if MySql supported full outer joins, which it doesn't. This has to be emulated by the union of right and left outer joins:

SELECT DISTINCT userId, userName FROM (
        SELECT sq1.userId,
        sq1.userName,
        sq1.unreadCount,
        IFNULL(sq2.readCount,0) AS readCount FROM
            (SELECT userId, userName, count(*) AS unreadCount FROM msg
            WHERE msgStatus = 'unread'
            GROUP BY userId, userName) sq1
        LEFT JOIN
            (SELECT userId, userName, count(*) AS readCount FROM msg
            WHERE msgStatus = 'read'
            GROUP BY userId, userName) sq2
        ON sq1.userId = sq2.userId
    UNION
        SELECT sq2.userId,
        sq2.userName,
        IFNULL(sq1.unreadCount,0) AS unreadCount,
        sq2.readCount FROM
            (SELECT userId, userName, count(*) AS unreadCount FROM msg
            WHERE msgStatus = 'unread'
            GROUP BY userId, userName) sq1
        RIGHT JOIN
            (SELECT userId, userName, count(*) AS readCount FROM msg
            WHERE msgStatus = 'read'
            GROUP BY userId, userName) sq2
        ON sq1.userId = sq2.userId
) sq
ORDER BY IF(unreadCount > readCount, unreadCount, 0) DESC, userId
;
1
forpas On

Sort by sum(msgStatus = 'unread') descending:

select userID, userName 
FROM msg 
GROUP BY userID
order by sum(msgStatus = 'unread') desc