在MySQL中按顺序排列有2个不同列的数据

Question

I am working with a discussion board where there will be a certain topic and the user can comment or upvote another comment. Each comment can also be replied by another user and will recieve notifications.

My notification table

• notification_id is the user_id of the logged in user that will receive the notification
• notification_from is the user_id of the one who commented or like the users post.
• notification_topic is the id of the topic plus the type of notification that will distinguish if it is a comment or an upvote
• notification_comment is the comment_id in the topic

+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+
| notification_id | notification_pic        | notification_name | notification_title                   | notification_user_id | notification_date | notification_from | notification_topic | notification_comment | type    | status |
+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+
|               1 | 32_1380182434_thumb.jpg | Sheena Salazar    | Chrysler files papers for share sale |                    2 | 1380188338        | 32                | 83_upvote          | 1                    | upvote  | read   |
|               2 | 32_1380182434_thumb.jpg | Sheena Salazar    | Chrysler files papers for share sale |                    2 | 1380188342        | 32                | 83_comment         | 1                    | comment | read   |
|               3 | 93_1379990163_thumb.jpg | vhon samson       | Chrysler files papers for share sale |                    2 | 1380188505        | 93                | 83_upvote          | 1                    | upvote  | read   |
|               4 | 93_1379990163_thumb.jpg | vhon samson       | Chrysler files papers for share sale |                    2 | 1380188509        | 93                | 83_comment         | 1                    | comment | read   |
|               5 | 93_1379990163_thumb.jpg | vhon samson       | Chrysler files papers for share sale |                    0 | 1380246975        | 93                | 83_comment         | 1                    | comment | unread |
|               6 | 93_1379990163_thumb.jpg | vhon samson       | Toyota and Nissan in vehicle recall  |                    2 | 1380247149        | 93                | 225_comment        | 3                    | comment | read   |
|               7 | default.gif             | kath aguilar      | Chrysler files papers for share sale |                    2 | 1380253584        | 7                 | 83_comment         | 1                    | comment | read   |
|               8 | default.gif             | kath aguilar      | Chrysler files papers for share sale |                   93 | 1380253870        | 7                 | 83_comment         | 2                    | comment | unread |
+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+

My query

This is how I query and group my table:

SELECT *, COUNT(notification_topic) AS topic_count
    FROM tbl_notification
    WHERE notification_user_id =  '{$_SESSION['id']}'
    GROUP BY notification_topic
    ORDER BY notification_date DESC 
    LIMIT 8

This is the result of the query:

+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+-------------+
| notification_id | notification_pic        | notification_name | notification_title                   | notification_user_id | notification_date | notification_from | notification_topic | notification_comment | type    | status | topic_count |
+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+-------------+
|               6 | 93_1379990163_thumb.jpg | vhon samson       | Toyota and Nissan in vehicle recall  |                    2 | 1380247149        | 93                | 225_comment        | 3                    | comment | read   |           1 |
|               2 | 32_1380182434_thumb.jpg | Sheena Salazar    | Chrysler files papers for share sale |                    2 | 1380188342        | 32                | 83_comment         | 1                    | comment | read   |           3 |
|               1 | 32_1380182434_thumb.jpg | Sheena Salazar    | Chrysler files papers for share sale |                    2 | 1380188338        | 32                | 83_upvote          | 1                    | upvote  | read   |           2 |
+-----------------+-------------------------+-------------------+--------------------------------------+----------------------+-------------------+-------------------+--------------------+----------------------+---------+--------+-------------+

See it on sqlfiddle.

What I need

I need to get the count so that I can come up with a notification like on facebook, like this:

My main problem is I can't ORDER it by notification_date before the GROUP BY because I need to isolate each notification_comment by its notification_topic. I want the latest user that commented or like each topic to be displayed. How will I do this?

Answer 1

If I've understood your problem correctly then you want the groupwise maximum, which can be obtained by selecting the identifying criteria in the group and then joining back to your table:

SELECT   *
FROM     tbl_notification NATURAL JOIN (
           SELECT   notification_topic,
                    notification_user_id,
                    MAX(notification_date) AS notification_date,
                    COUNT(*)               AS topic_count
           FROM     tbl_notification
           WHERE    notification_user_id = 2
           GROUP BY notification_topic,
                    notification_user_id
         ) AS t
ORDER BY notification_date DESC 
LIMIT    8

See it on sqlfiddle.