Select child rows by skipping first 5 records based on parent_id

Question

For the following query I get the error This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

SELECT * FROM `wp_dash_competition_versions` _versions
WHERE DATEDIFF(CURRENT_TIMESTAMP, _versions.created_at) > 20 AND
id NOT IN (
    SELECT id FROM `wp_dash_competition_versions` WHERE competition_id = _versions.competition_id
    ORDER BY created_at LIMIT 5
)

Other SO articles recommend the use of a join, however the join query doesn't apply the limit to a given parent_id.

Is it possible to achieve a single query that selects rows from a child table skipping the first 5 rows for a given parent_id?

I'm developing the query primarily for a delete operation to prune the table. I need the select first to ensure the statement is correct.

CREATE TABLE `wp_dash_competition_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `competition_id` bigint(20) unsigned DEFAULT NULL,
  `competition_serialised` mediumtext COLLATE utf8mb4_unicode_520_ci,
  `created_user_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

You say it doesn't apply, but using a JOIN does appear to be the answer https://stackoverflow.com/a/17892886/3854195 — Morpheus, Mar 04 '20 at 20:47
The video query didn't need to include a subquery, it doesn't apply because it doesn't perform an exclude on comp versions by the latest against a parent ID — timmah.faase, Mar 04 '20 at 21:29

score 0 · Answer 1 · answered Mar 05 '20 at 20:22

SELECT _version.*
FROM `wp_dash_competition_versions` _version
INNER JOIN (
              SELECT competition_id, GROUP_CONCAT(id ORDER BY created_at DESC) _versions
              FROM `wp_dash_competition_versions` GROUP BY competition_id
) group_versions
ON _version.competition_id = group_versions.competition_id
AND FIND_IN_SET(id, _versions) > 5
AND DATEDIFF(CURRENT_TIMESTAMP, created_at) > 20

Found in answer https://stackoverflow.com/a/15585351/972457

Select child rows by skipping first 5 records based on parent_id

1 Answers1