0

I'm not sure I formulated the question properly, but anyway.

I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query that would limit results for rows with the same comment_parent to 5? So that I don't get 5 comments in total, but instead get 5 comments for each group of comments with the same comment_parent value?

I found a lot of (kind of) similar questions both here and on SO, but all those situations were different in one way or another, or I just wasn't able to "project" the answers from there to my case. So forgive me if this is just another duplicate in your opinion. I would really like to get an answer for my case precisely.

Table sample:

CREATE TABLE `level_2` (
  `id` int(11) NOT NULL,
  `comment_id` int(11) DEFAULT NULL,
  `comment_parent` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_photo` varchar(2083) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `url` varchar(2083) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment_text` varchar(16834) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment_date` timestamp NULL DEFAULT NULL
)

Data sample:

INSERT INTO `level_2` (`id`, `comment_id`, `comment_parent`, `user_id`, `user_name`, `user_photo`, `url`, `comment_text`, `comment_date`) VALUES
(1, 270, 269, 11223344, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(2, 271, 269, 22334455, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(3, 272, 269, 33445566, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(4, 273, 269, 11111111, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(5, 274, 269, 22222222, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(6, 275, 269, 11111111, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(7, 276, 269, 22222222, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(8, 280, 268, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(9, 281, 268, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(10, 282, 278, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52');

My query so far:

SELECT * FROM level_2 LIMIT 5

The output I'm getting (roughly):

(`id`, `comment_id`, `comment_parent`, `user_id`, `user_name`, `user_photo`, `url`, `comment_text`, `comment_date`) VALUES
(1, 270, 269, 11223344, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(2, 271, 269, 22334455, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(3, 272, 269, 33445566, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(4, 273, 269, 11111111, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(5, 274, 269, 22222222, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45');

The output I'd like to get (roughly):

(`id`, `comment_id`, `comment_parent`, `user_id`, `user_name`, `user_photo`, `url`, `comment_text`, `comment_date`) VALUES
(1, 270, 269, 11223344, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(2, 271, 269, 22334455, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(3, 272, 269, 33445566, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(4, 273, 269, 11111111, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(5, 274, 269, 22222222, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(8, 280, 268, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52'),
(9, 281, 268, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-22 04:04:45'),
(10, 282, 278, 35990230, 'Username', '/userpic.jpg', '/example_page', 'sample text', '2019-02-27 12:27:52');
  • I'm not sure how it can help in this case, but ok. Please see the edit. – COOLak Feb 28 '19 at 18:49
  • What should happen if the first 5 rows ('SELECT * FROM level_2 LIMIT 5') returns duplicated comment_parent values? – McNets Feb 28 '19 at 18:56
  • Nothing. I just need to output groups of the maximum of 5 rows with duplicated comment_parent values. That's my goal. – COOLak Feb 28 '19 at 19:01
  • I'm using Mysql 8. //P.S. the results must include rows without that duplicating value as well, but those duplicated must be limited to 5 per each group. – COOLak Feb 28 '19 at 19:04
0

I can't test it because on-line fiddle do not has the last version, but if you are on MySQL 8.0.14 you could try by using a LATERAL join:

SELECT
    *
FROM
    (SELECT DISTINCT comment_parent
     FROM level_2) cp,
LATERAL
    (SELECT *
     FROM   level_2
     WHERE  comment_parent = cp.comment_parent
     LIMIT 5) l2
;

If not, you could try with ROW_NUMBER:

WITH CT AS
(
    SELECT *,
           row_number() OVER (PARTITION BY comment_parent) rn
    FROM   level_2
)
SELECT * 
FROM   CT
JOIN   (SELECT DISTINCT comment_parent FROM level_2 LIMIT 5) cp
ON     CT.comment_parent = cp.comment_parent
WHERE  rn <=5 ;
id | comment_id | comment_parent |  user_id | user_name | user_photo   | url           | comment_text | comment_date        | rn | comment_parent
-: | ---------: | -------------: | -------: | :-------- | :----------- | :------------ | :----------- | :------------------ | -: | -------------:
 8 |        280 |            268 | 35990230 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-27 12:27:52 |  1 |            268
 9 |        281 |            268 | 35990230 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-22 04:04:45 |  2 |            268
 1 |        270 |            269 | 11223344 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-22 04:04:45 |  1 |            269
 2 |        271 |            269 | 22334455 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-27 12:27:52 |  2 |            269
 3 |        272 |            269 | 33445566 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-27 12:27:52 |  3 |            269
 4 |        273 |            269 | 11111111 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-27 12:27:52 |  4 |            269
 5 |        274 |            269 | 22222222 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-22 04:04:45 |  5 |            269
10 |        282 |            278 | 35990230 | Username  | /userpic.jpg | /example_page | sample text  | 2019-02-27 12:27:52 |  1 |            278

db<>fiddle here

| improve this answer | |
  • Thank you! The first variant worked, except I had to remove the first occurence of LIMIT 5 from the query, otherwise it would limit the whole response to 5 groups of comments, while I needed infinite groups each limited to 5 results. Please edit and I'll accept the answer. – COOLak Mar 1 '19 at 11:41
  • I've edited the answer, have a look. – McNets Mar 1 '19 at 12:05
  • Would you mind providing an example how to order each group of results by comment_id? I managed to make it output the appropriate group of comments (the last 5) by using SELECT * FROM (SELECT DISTINCT comment_parent FROM level_2 WHERE url = '".$url."') cp, LATERAL (SELECT * FROM level_2 WHERE url = '".$url."' AND comment_parent = cp.comment_parent ORDER BY comment_id DESC LIMIT 5) l2, however, I also need each group of last 5 comments to be DESC ordered. Can't figure how to achieve this. – COOLak Mar 2 '19 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.