CODEWITHSUNDEEP
mysqlin-operator
Selvakumar M
Selvakumar M

Reputation: 13

MySQL LIMIT inside IN Operator

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'

Upvotes: 0

Views: 152

Answers (3)

Rick James
Rick James

Reputation: 142298

Let's turn it inside out:

select  distinct T1.column1
    FROM  
        ( SELECT  T2.Table1id
            from  Table2 T2
            where  (conditions)
            order by  T2.column
            limit  2
        ) AS x
    JOIN  Table1 AS T1  ON T1.table1id = x.Table1id

The inner (derived) table may need DISTINCT.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

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

Upvotes: 1

blabla_bingo
blabla_bingo

Reputation: 2152

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

Upvotes: 2

Related Questions