Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql RAND() LIMIT

Tags:

mysql

Hello I have a query like this:

    SELECT otel_id 
        FROM YAZILIM_menu_icerik 
        WHERE YAZILIM_menu_icerik.menu_id = 39 
        AND otel_id IN (
                        SELECT otel_id 
                        FROM YAZILIM_menu_icerik 
                        WHERE menu_id =$id 
                        ORDER BY RAND()
                        ) 
        LIMIT 0,20

It should display randomly 20 hotels each time it works but instead , i'm ending up getting the same 20 hotel each time. Long story short RAND() doesn't seem to work and I can't seem to find any logical error.

EDIT: Problem solved. Mysql doesn't see inner RAND() so here's the correct way to do it:

SELECT otel_id 
    FROM YAZILIM_menu_icerik 
    WHERE YAZILIM_menu_icerik.menu_id = 39 
    AND otel_id IN (
                    SELECT otel_id 
                    FROM YAZILIM_menu_icerik 
                    WHERE menu_id =$id 
                   )
ORDER BY RAND() 
LIMIT 0,20
like image 311
FreshPro Avatar asked Dec 10 '12 08:12

FreshPro


1 Answers

Your ORDER BY and LIMIT clauses should be together:

SELECT otel_id 
FROM YAZILIM_menu_icerik 
WHERE YAZILIM_menu_icerik.menu_id = 39
AND otel_id IN 
(
    SELECT otel_id 
    FROM YAZILIM_menu_icerik 
    WHERE menu_id = $id 
)
ORDER BY RAND()
LIMIT 20

It makes no sense to have a bare ORDER BY in a subquery with no LIMIT clause because the ordering is not necessarily preserved in the results of the outer query.

like image 159
Mark Byers Avatar answered Nov 07 '22 05:11

Mark Byers