CODEWITHSUNDEEP
mysqlrandomsql-limit
Franco
Franco

Reputation: 2926

LIMIT by random number between 1 and 10

Essentially, I want to return X number of records from the last 21 days, with an upper limit of 10 records.

How do I add a random LIMIT to a query in MySQL?

Here's my query, with X for the random number 1-10.

SELECT releases.id, COUNT(charts_extended.release_id) as cnt FROM releases
INNER JOIN charts_extended
ON charts_extended.release_id=releases.id
WHERE DATEDIFF(NOW(), releases.date) < 21
GROUP BY releases.id
ORDER BY RAND()
LIMIT 0, X

I tried using RAND() * 10 + 1, but it gives a syntax error.

Is there any way to do this using pure SQL; ie without using an application language to "build" the query as a string and have the application language fill in X programmatically?

Upvotes: 1

Views: 1367

Answers (2)

Bohemian
Bohemian

Reputation: 425003

Eureka...

In pseudo code:

  • execute a query to select 10 random rows
  • select from that assigning a row number 0-9 using a user defined variable to calculate that
  • cross join with a single hit on rand() to create a number 0-9 and select all rows with row number less than or equal to that number

Here's the essence of the solution (you can adapt your query to work with it:

select * from (
  select *, (@row := coalesce(@row + 1, 0)) row from (
    // your query here, except simply LIMIT 10
    select * from mytable
    order by rand()
    limit 10
  ) x
) y
cross join (select rand() * 10 rand) z
where row <= rand

See SQLFiddle. Run it a few times and you'll see you get 1-10 random rows.

If you don't want to see the row number, you can change the outer select * to select only the specific columns from the inner query that you want in your result.

Upvotes: 1

Naveed Ramzan
Naveed Ramzan

Reputation: 3593

Your query is correct but you need to update limit clause. 

$query = "SELECT releases.id, COUNT(charts_extended.release_id) as cnt FROM releases
INNER JOIN charts_extended
ON charts_extended.release_id=releases.id
WHERE DATEDIFF(NOW(), releases.date) < 21
GROUP BY releases.id
ORDER BY RAND()
LIMIT 0,".rand(1,10);

and then execute this query.

Upvotes: 0

Related Questions