Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005

    Unanswered: select rows where id in (select...limit x) limit z Not Allowed?


    I have a table with videos (id, title, views)
    I want to display the most viewed videos.
    I don't want to show always the same top ten por example.
    The solution is to select the top 30 and then get 5 random from them.

    MySQL version is 5.0.45 (lastest)

    The query is:
    SELECT * FROM video WHERE id IN (
    SELECT id
    FROM video v
    ORDER BY visitas
    LIMIT 30) x
    ORDER BY rand()
    LIMIT 10

    (alias X and V are mandatory for MySQL)

    The problem is I get this error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

    Is there any solution?
    The only way is by using 2 queries and PHP. Isn't it?
    The first one to get top 30 (just id's) and then top 10 where id IN (id´s from first query).

    Thanks a lot!

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    my advice: return the top 30, then use php to pick 10 at random

    keeps the query simple | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts