I have a table like this:
// notifications
+----+--------+-----------+---------+--------------------+
| id | money | post_id | user_id | belongs_to_user_id |
+----+--------+-----------+---------+--------------------+
| 1 | 5 | 1 | 123 | 101 |
| 2 | 10 | 2 | 123 | 101 |
| 3 | -2 | 4 | 456 | 101 |
| 5 | -2 | 2 | 456 | 101 |
| 6 | -2 | 3 | 123 | 101 |
| 7 | 5 | 4 | 789 | 101 |
| 8 | 10 | 4 | 789 | 101 |
+----+--------+-----------+---------+--------------------+
And here is my query:
SELECT * FROM notifications
WHERE belongs_to_user_id = 101
GROUP BY post_id, user_id
ORDER BY id DESC
LIMIT 3
The current output should be something like this:
+----+--------+-----------+---------+--------------------+
| 5 | -2 | 2 | 456 | 101 |
| 6 | -2 | 3 | 123 | 101 |
| 8 | 10 | 4 | 789 | 101 |
+----+--------+-----------+---------+--------------------+
The seventh row is grouped and we cannot see it in the result. That's exactly the problem. Here is the expected result:
+----+--------+-----------+---------+--------------------+
| 5 | -2 | 2 | 456 | 101 |
| 6 | -2 | 3 | 123 | 101 |
| 7 | 5 | 4 | 789 | 101 |
| 8 | 10 | 4 | 789 | 101 |
+----+--------+-----------+---------+--------------------+
If I remove GROUP BY
, then the fifth will be omitted. So here is the logic:
I want to the last three rows (regardless grouping). In other word, Emm ... it's hard to say, I want to select grouped rows (but not counting in
LIMIT
).
Any idea how can I do that?
It shows comma separated id by groups
SELECT
GROUP_CONCAT(id),
post_id
FROM notifications
WHERE belongs_to_user_id = 101
GROUP BY post_id, user_id
ORDER BY id DESC
LIMIT 3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With