Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I get ids of grouped rows?

Tags:

sql

mysql

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?

like image 539
Martin AJ Avatar asked Apr 05 '18 09:04

Martin AJ


1 Answers

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
like image 199
Gopal Rathod Avatar answered Oct 04 '22 23:10

Gopal Rathod