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.

Answer 1

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`