MySQL LIMIT inside IN Operator-CodePudding

select distinct column1 
from Table1 
where Table1id in ((select T2.Table1id 
                    from Table2 T2 
                    where (conditions) 
                    order by T2.column) 
                   limit 2
                  );

I cannot use limit inside the In operator. Do we have any other way to limit inside IN operator? Or do we have any other way without using IN and also without using any joins?

Error (while using limit inside the In Operator):-

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

CodePudding user response：

For non-specific MySQL versions, Ergest gave you a good solution of using JOIN. Here is another workaround in which an outer layer is used on top of the derived table.

select distinct column1 
from Table1 
where Table1id in (select id 
                   from 
                       (select  T2.Table1id as id
                        from Table2 T2 
                        where (conditions) 
                        order by T2.column 
                        limit 2) tb);

PS: this trick can be used to bypass the ERROR 1093 (HY000): You can't specify target table 'terms' for update in FROM clause

CodePudding user response：

What a weird restriction. Well, you can simply use an ad-hoc view (aka. WITH clause or CTE):

with limited as 
(
    select T2.Table1id
    from Table2 T2
    where (conditions)
    order by T2.column
    limit 2
)
select distinct column1
from Table1
where Table1id in (select Table1id from limited);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cc148fae3a1089324446ec792e1476e2