MYSQL-Error 'LIMIT & IN/ALL/ANY/SOME subquery'

Question

I have a MYSQL-Query which have to go but it returns the error 'LIMIT & IN/ALL/ANY/SOME subquery'

SELECT *
FROM page p
WHERE p.page_id IN (
    SELECT c.cl_from
    FROM categorylinks
    WHERE c.cl_from = p.page_id
    AND c.cl_to IN (
        'Art-Rock-Band',
        'Echo-Pop-Preisträger',
        'Englische_Band',
        'Genesis_(Band)',
        'Grammy-Preisträger',
        'Peter_Gabriel',
        'Phil_Collins',
        'Popband',
        'Progressive-Rock-Band',
        'Rock_and_Roll_Hall_of_Fame'
    )
    LIMIT 0,5
);

To explain what i want: I want just have 5 results of every category which is in the IN-Clausle.

MYSQL-Version: 5.5.44-0ubuntu0.14.04.1 - (Ubuntu)

you can't use limits in subqueries: https://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html That's docs for 5.0, but the restriction is still in 5.7, so you're S.O.L. — Marc B, Sep 24 '15 at 21:53
Did you try my answer in your original post? http://stackoverflow.com/questions/32770125/sql-set-limit-for-a-column/32770568#32770568 It shouldn't throw that error because there's no subquery. — CSS, Sep 24 '15 at 21:59

Philipp · Accepted Answer · 2015-09-24T22:27:50.633

1

You can't use IN with subqueries which have an limit. The easiest (but maybe not the fastest - don't tested it..) solution would be to add a second subquery around the one with the limit, which have no limit.

So this:

field IN (SELECT ... FROM x LIMIT 5)

would become this

field IN (SELECT * FROM (SELECT ... FROM x LIMIT 5) temp_table)

In your case you would become the following:

SELECT *
FROM page p
WHERE p.page_id IN (SELECT * FROM(
    SELECT c.cl_from
    FROM categorylinks c
    WHERE c.cl_from = p.page_id
    AND c.cl_to IN (
        'Art-Rock-Band',
        'Echo-Pop-Preisträger',
        'Englische_Band',
        'Genesis_(Band)',
        'Grammy-Preisträger',
        'Peter_Gabriel',
        'Phil_Collins',
        'Popband',
        'Progressive-Rock-Band',
        'Rock_and_Roll_Hall_of_Fame'
    )
    LIMIT 0,5
) temp_table);

You could also try to transform your subquery in an inner join - that should also work.

edited Sep 24 '15 at 22:27

answered Sep 24 '15 at 21:59

Philipp

15,377
4
35
52

You forgot to alias categorylinks with the `c` you prepend its fields with. I take it back, @name forgot to... – CSS Sep 24 '15 at 22:11
it returns after your alias edit the error "Every derived table must have its own alias" – Name Sep 24 '15 at 22:24
@Philipp now it returns "Unknown column 'p.page_id' in 'where clause'". I really don't know why cause the page.page_id column exists – Name Sep 24 '15 at 22:30
@Name, p.page_id belong to the table "page", you can't use it there : "c.cl_from = p.page_id" – Basile Sep 24 '15 at 22:35
ok that means there is no possible way to solve the problem :/? – Name Sep 24 '15 at 22:36
I think, you dont need that part. just remove `c.cl_from = p.page_id AND` – Philipp Sep 24 '15 at 22:38
@Name: please check my EDIT – Basile Sep 24 '15 at 22:42
@Philipp, I think what Name wants is 5 "pages" for each "category" – Basile Sep 24 '15 at 22:44
As mentioned in my EDIT, if what you want is 5 results per category, I suggest you have a look at this post : http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Basile Sep 24 '15 at 22:50

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

EDIT: it seems that your request has been misunderstood.

Even if there was no syntax error, the query you propose couldn't return 5 results per category.

To do so, I suggest you have a look at this post : Get top n records for each group of grouped results

As per the error message, you can't use limits in subqueries.

You should consider using JOINS in your query instead :

SELECT p.*
FROM categorylinks c
JOIN page p ON p.page_id = c.cl_from
WHERE c.cl_from = p.page_id
AND c.cl_to IN (
    'Art-Rock-Band',
    'Echo-Pop-Preisträger',
    'Englische_Band',
    'Genesis_(Band)',
    'Grammy-Preisträger',
    'Peter_Gabriel',
    'Phil_Collins',
    'Popband',
    'Progressive-Rock-Band',
    'Rock_and_Roll_Hall_of_Fame'
)
LIMIT 0,5;

edited May 23 '17 at 12:15

Community

1
1

answered Sep 24 '15 at 22:03

Basile

326
1
7

You forgot to alias categorylinks with the `c` you prepend its fields with. I take it back, @name forgot to... – CSS Sep 24 '15 at 22:10
It returns just 5 result. Not 5 results per category. – Name Sep 24 '15 at 22:25

MYSQL-Error 'LIMIT & IN/ALL/ANY/SOME subquery'

2 Answers2