Limit MySQL Group Results Based on Column Value

group bylimitsMySQLmysql-8.0select

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');

Best Answer

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

YOUR ACTUAL QUESTIONS

Does this actually do the same or is my understanding wrong?

Is one slower/faster in larger tables

Since both queries are the same, there is no difference

Does the result of offset change when I do WHERE column=1 (say column has >100 different values)

Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

Using LIMIT does not change any result sets. They simply navigate within the result set.

This query

SELECT id,name,description FROM tablename ORDER BY id LIMIT 1000,25

would be different from

SELECT * FROM (SELECT id,name,description FROM tablename LIMIT 1000,25) A ORDER BY id;

because the LIMIT is being applied at a different stage.

The first query returns nothing if tablename has less 1000 rows

The second query returns nothing if the subquery has less 1000 rows

CONCLUSION

You will have to sculpt the query to make sure you are sorting data at the right stage

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

MySQL – Limit 1000,25 vs Limit 25 Offset 1000

YOUR ACTUAL QUESTIONS

CONCLUSION

Related Question