Solution to MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Question

For some reason MYSQL doesn't support LIMIT inside a subquery:

 SELECT m.* 
 FROM `my_table` m
 WHERE m.`id` IN (
   SELECT o.`id`
   FROM (SELECT DISTINCT i.`id`, i.`label`, i.`client`, i.`place`
     FROM `my_table` i
     ORDER BY i.`label`, -i.`client` DESC, -i.`place` DESC) o
   WHERE m.`label` = o.`label` LIMIT 1
 );

I've tried using join from this link: INNER JOIN INSTEAD OF IN(LIMIT error) but not succeeded. Has anyone any clues for it? Thanks.

The error for the piece of code I've provided is: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery" — IleNea, Aug 09 '20 at 16:18
Have you try to use the function [`ROW_NUMBER()`](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number)? — asantz96, Aug 09 '20 at 16:20
I just replaced LIMIT 1 with ROW_NUMBER() and it sill says I have errors in my query.. — IleNea, Aug 09 '20 at 16:23
It is a little bit more difficult you have to use it with a `PARTITION`. Please review this [documentation](https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/). — asantz96, Aug 09 '20 at 16:26

score 2 · Accepted Answer · answered Aug 09 '20 at 16:29

Since the subquery returns only 1 row with 1 column there is no need for IN.
You can use =:

 SELECT m.* 
 FROM `my_table` m
 WHERE m.`id` = (
   SELECT o.`id`
   FROM (
     SELECT DISTINCT i.`id`, i.`label`, i.`client`, i.`place`
     FROM `my_table` i
     ORDER BY i.`label`, -i.`client` DESC, -i.`place` DESC) o
   WHERE m.`label` = o.`label` LIMIT 1
 );

But as it is written, your query uses LIMIT without ORDER BY (you do use ORDER BY in the inner subquery where it is useless).
Do you mean to do something like this:

SELECT m.* 
FROM `my_table` m
WHERE m.`id` = (
  SELECT o.`id`
  FROM (
    SELECT DISTINCT i.`id`, i.`label`, i.`client`, i.`place`
    FROM `my_table` i
  ) o
  WHERE m.`label` = o.`label` 
  ORDER BY o.`label`, -o.`client` DESC, -o.`place` DESC
  LIMIT 1
);

Also ordering by the negative value of a column descending is equivalent to ordering just ascending, so the ORDER BY clause can be simplified to:

ORDER BY o.`label`, o.`client`, o.`place`

Solution to MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

1 Answers1