3

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

3
  • 1) Convert subquery to view. 2) Limit without ordering by unique field/expression gives random records, not last ones.
    – Akina
    Feb 1, 2019 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, 2019 at 19:05
  • 2
    It would be much better if you provided the tables' data as text, not images. Feb 1, 2019 at 20:27

2 Answers 2

1

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.

4
  • 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, 2019 at 20:05
  • Two more methods added that should work with arbitrary limit. Feb 1, 2019 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, 2019 at 20:36
  • No, without the where x.ran_resid is null, it's going to give you the opposite result. Feb 2, 2019 at 1:20
-1

you cannot use limit in a subquery so you must use a left join with views table consist of two records you want to except on resid conditions then you pickup records that second table is null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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