在MySQL中按顺序排列有2个不同列的数据
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_idis the user_id of the logged in user that will receive the notificationnotification_fromis the user_id of the one who commented or like the users post.notification_topicis the id of the topic plus the type of notification that will distinguish if it is a comment or an upvotenotification_commentis 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?
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.