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

Question

This is the php code what I'm using

    $Last_Video         = $db->fetch_all('
    SELECT VID, thumb
    FROM video
    WHERE VID IN (
        SELECT VID
        FROM video
        WHERE title LIKE "%'.$Channel['name'].'%"
        ORDER BY viewtime DESC
        LIMIT 5)
    ORDER BY RAND()
    LIMIT 1
');

This is the error what give me

 Message:   Error during SQL execution: SELECT VID, thumb FROM video WHERE VID IN ( SELECT VID FROM video WHERE title LIKE "%funny%" ORDER BY viewtime DESC LIMIT 5) ORDER BY RAND() LIMIT 1<br />
 MySQL Error:   This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'<br />
MySQL Errno:    1235

Can you please supply the whole code. How is $db set for example? — bestprogrammerintheworld, Jul 26 '13 at 23:58

score 211 · Accepted Answer · answered Jul 27 '13 at 00:10

211

Instead of using IN, you can use JOIN

SELECT v.VID, v.thumb
FROM video AS v
INNER JOIN
     (SELECT VID
     FROM video
     WHERE title LIKE "%'.$Channel['name'].'%"
     ORDER BY viewtime DESC
     LIMIT 5) as v2
  ON v.VID = v2.VID
ORDER BY RAND()
LIMIT 1

answered Jul 27 '13 at 00:10

Mike Brant

70,514
10
99
103

i try this code and works perfect ... this is the way how i use it $Last_Video = $db->fetch_all('SELECT v.VID, v.thumb FROM video AS v INNER JOIN (SELECT VID FROM video WHERE title LIKE "%'.$Channel['name'].'%" ORDER BY viewtime DESC LIMIT 5) as v2 ON v.VID = v2.VID ORDER BY RAND() LIMIT 1'); foreach($Last_Video as $Video) { $Array = array( "VID" => $Video['VID'], "Thumb" => $Video['thumb'], "Total_Videos2" => $Total_Videos['num'], "Last_Update_Data" => time() ); – Mihai Viteazu Jul 27 '13 at 00:32
parameters should be used to avoid SQL injection – Benoit Duffez Aug 13 '19 at 17:24

score 174 · Answer 2 · edited May 23 '17 at 09:48

174

You can use below to bypass this error.

$Last_Video = $db->fetch_all('
    SELECT VID, thumb
    FROM video
    WHERE VID IN (select * from (
        SELECT VID
        FROM video
        WHERE title LIKE "%'.$Channel['name'].'%"
        ORDER BY viewtime DESC
        LIMIT 5) temp_tab)
    ORDER BY RAND()
    LIMIT 1
');

edited May 23 '17 at 09:48

fabpico

2,628
4
26
43

answered Jul 16 '14 at 09:47

Surender

1,829
1
11
2

27

not sure why the db engine can't accommodate something like this without the need to wrap a subquery in a subquery - which just seems stupid. but hey, this works so thanks. – But those new buttons though.. Mar 30 '16 at 20:42
6

Unfortunately this won't work if you are trying to reference outer select statement column from inner select stament. Example: `select p1.categoryid, p1.productid from products p1 WHERE p1.productid IN (select * from (select p2.productid from products p2 WHERE p2.categoryid=p1.categoryid order by p2.categoryid asc, p2.unitprice desc limit 3) as tabelka);` – Tomasz Mularczyk Sep 28 '16 at 19:06

score 5 · Answer 3 · edited Jul 16 '16 at 20:52

5

You don't need a subquery here. Try this:

 SELECT VID, thumb
 FROM video
 WHERE title LIKE "%'.$Channel['name'].'%"
 ORDER BY RAND() DESC
 LIMIT 1

In MySQL 5.0.26 and later, you will get an error:

MySQL does not support LIMIT in subqueries for certain subquery operators:

Reference.

edited Jul 16 '16 at 20:52

halfer

19,824
17
99
186

answered Jul 26 '13 at 23:58

echo_Me

37,078
5
58
78

4

That doesn't meet the original goal of selecting one record at random for the 5 returned from subquery. – Mike Brant Jul 27 '13 at 00:01
1

The edit still does not support the ability to limit the random selection to only those records with 5 highest values for `viewtime` – Mike Brant Jul 27 '13 at 00:12

score 3 · Answer 4 · edited Feb 17 '20 at 13:36

3

add this is your in condition

(SELECT * FROM (
    SELECT * FROM table ORDER BY id DESC LIMIT 50
) sub
ORDER BY id ASC)

edited Feb 17 '20 at 13:36

Nikolai Shevchenko

7,083
8
33
42

answered Feb 17 '20 at 13:11

Vasim Walikar

31
1

Why, what do you get with this? – Sebastián Palma Sep 15 '20 at 14:37
4

apparently the db engine can't handle a limit in a subquery, but does in a subquery in a subquery. Now we not only have to deal with sql injection, but also sql inception... – SomeOne_1 Dec 15 '21 at 08:16
I'm surprised this didn't get more upvotes. I mean it seems stupid that this is a solution, but we can blame MySQL for that. This is a quick and easy solution for anyone trying to get their query working. – Harry Wood Nov 03 '22 at 09:36
I ... I ... I can't ... Thank you so much :) – resi Feb 22 '23 at 08:27

score 0 · Answer 5 · answered Oct 25 '13 at 08:46

0

Why you cant use simple: ?

SELECT v.VID, v.thumb
FROM video as v
WHERE title LIKE "%'.$Channel['name'].'%"
ORDER BY viewtime DESC
LIMIT 5

what for subqueries here?

answered Oct 25 '13 at 08:46

Vladimir Krasnobaev

19
1

2

Because your query doesn't accomplish what he is trying to do. – Jeff Ryan Nov 25 '18 at 19:34

score 0 · Answer 6 · edited Aug 13 '23 at 14:15

0

mysql is disabled read it ORACLE

DELETE FROM wall_orders WHERE order_id IN (
SELECT order_id FROM (SELECT order_id, COUNT(orders_products_id) as cnt FROM wall_orders_products GROUP BY order_id ORDER BY cnt DESC LIMIT 1000) y1 WHERE cnt > 170 LIMIT 1000)

235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Simple SQL and not possible

edited Aug 13 '23 at 14:15

Makyen

31,849
12
86
121

answered Dec 26 '21 at 17:46

Kamil Dąbrowski

984
11
17

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

6 Answers6

Linked

Related