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');
4
  • I'm not sure how it can help in this case, but ok. Please see the edit.
    – COOLak
    Feb 28, 2019 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, 2019 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, 2019 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, 2019 at 19:04

1 Answer 1

1

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

3
  • 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, 2019 at 11:41
  • I've edited the answer, have a look.
    – McNets
    Mar 1, 2019 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, 2019 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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