MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
PhpMysqlPhp Problem Overview
this is the code what im 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
how i can fix this problem ? its other way to make it ... so i dont get the error ...
Php Solutions
Solution 1 - Php
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
Solution 2 - Php
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
');
Solution 3 - Php
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:
Solution 4 - Php
add this is your in condition
(SELECT * FROM (
SELECT * FROM table ORDER BY id DESC LIMIT 50
) sub
ORDER BY id ASC)
Solution 5 - Php
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?
Solution 6 - Php
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