LIMIT by random number between 1 and 10

Question

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?

Why would you want to do this (and LIMIT without ORDER BY is anyway meaningless)? — Strawberry, Jan 18 '16 at 07:38
Would like to return a random number of results, with a max limit of 10 — Franco, Jan 18 '16 at 07:47
There will be, designing the query first - i updated the question with the actual query if that helps? — Franco, Jan 18 '16 at 07:54
Handle this problem there. Just make a for loop of a random size — Strawberry, Jan 18 '16 at 08:00
already answered I think, http://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values in my opinion get the rows from MySQL, randomize the presentation using PHP — Paul Maxwell, Jan 18 '16 at 08:08

score 1 · Accepted Answer · answered Jan 24 '16 at 06:16

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.

Naveed Ramzan · Answer 2 · 2016-01-18T08:09:54.220

0

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.

edited Jan 18 '16 at 08:09

answered Jan 18 '16 at 07:59

Naveed Ramzan

3,565
3
25
30

This isn't actually what I need - I need to return a random number (between 1 and 10) of results – Franco Jan 18 '16 at 08:01
I have updated the answer, you need to pass the X variable in limit using PHP. – Naveed Ramzan Jan 18 '16 at 08:10

LIMIT by random number between 1 and 10

2 Answers2