1

I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to use LIMIT in my sub-query which is giving this error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'`

Here's my query -

select r.res_id, r.res_name
from Resource AS r
where r.ran_resId NOT IN 
      ( select v.ran_resId
        from views AS v
        order by v.viewsid
        limit 2
      )
group by r.ran_resId

I've google it and found out that LIMIT can't be use in SubQuery in MySql, And as an alternate I could use JOIN however I'm not able to create the query to get the desired results.
A help is highly appreciated. PS - I'm using MySql 5.6

enter image description here

  • 1) Convert subquery to view. 2) Limit without ordering by unique field/expression gives random records, not last ones. – Akina Feb 1 at 18:54
  • 2
    I'm using order-by as well in my subquery , I've updated the question thanks for your suggestion. – Sumit Feb 1 at 19:05
  • 2
    It would be much better if you provided the tables' data as text, not images. – ypercubeᵀᴹ Feb 1 at 20:27
0

If 2 is a fixed value and you'll never need to compare against 3, 4 or more items in the future, then you could convert the subquery to a derived table and find the 2 values with aggregation:

select r.res_id, r.res_name
from Resource as r
     join
     ( select min(lim.ran_resid) as ran_resid_1,
              max(lim.ran_resid) as ran_resid_2 
       from
         ( select v.ran_resid
           from views as v
           order by v.viewsid
           limit 2
         ) as lim
     ) as x
     on r.ran_resid NOT IN (x.ran_resid_1, x.ran_resid_2)
 ;  

A more general solution (where you can use any LIMIT and not just 2), is to write the antijoin with the LEFT JOIN / IS NULL method:

select r.res_id, r.res_name
from Resource as r
     left join
     ( select v.ran_resid
       from views as v
       order by v.viewsid
       limit 5
     ) as x
     on r.ran_resid = x.ran_resid
where x.ran_resid is null
 ; 

An NOT EXISTS version should work as well but I'm not sure that it would be very efficient, due to the double nesting:

select r.res_id, r.res_name
from Resource as r
where not exists
  ( select 1
    from
      ( select v.ran_resid
        from views as v
        order by v.viewsid
        limit 5
      ) as x
    where r.ran_resid = x.ran_resid
  ) ;

Also note that I removed the last group by r.ran_resId from your query.

It makes no sense to group by a and select b, c in a query. And while MySQL allows it, it is a guaranteed way to get garbage results.

  • 2 is not fixed actually it can be 5 or 10. It's 5 actually but for the sake of example i mentioned 2. Also the table on which I'm trying to compare has got 'n' number of data and I only need to compare last 5 records. – Sumit Feb 1 at 20:05
  • Two more methods added that should work with arbitrary limit. – ypercubeᵀᴹ Feb 1 at 20:22
  • The 2nd query you've posted left join is working fine however I'm unable to understand where x.ran_resid is null this line, the query is working fine without this. need to test it with 4-5 more cases though. – Sumit Feb 1 at 20:36
  • No, without the where x.ran_resid is null, it's going to give you the opposite result. – ypercubeᵀᴹ Feb 2 at 1:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.