CODEWITHSUNDEEP
mysqlsql
user3423909
user3423909

Reputation: 189

MySQL take a range of values based on a column of descending numbers, and then randomize that range

I'm having trouble implementing in my code what should be a simple query. I'm trying to take a range of values, say numbered sequentially 1-20 in a column named front_weight and then randomize that query by that column. So far what I've come up with is:

$sql_rand_limit_range = "SELECT * FROM posts 
                         WHERE active='y' 
                         AND front_weight > 0 
                         AND front_weight 
                         IN (SELECT front_weight 
                         FROM posts ORDER BY front_weight DESC LIMIT 0, 20) 
                         ORDER BY RAND()";

I get this error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am running MySQL Version 5.1 by the way. I then try to monkey around with the query a little bit trying something like this which in my eyes should work:

$sql_rand_limit_range = "SELECT * FROM posts 
                         WHERE active='y' 
                         AND front_weight > 0
                         ORDER BY RAND() front_weight DESC
                         LIMIT 0, 20";


$sql_rand_limit_range = "SELECT * FROM posts 
                         WHERE active='y' 
                         AND front_weight > 0
                         ORDER BY front_weight DESC
                         LIMIT 0, 20 
                         RAND()";

But you SQL ninjas can see how far that got me. Any ideas or will I have to do something a little bit more drastic using Php code like randomizing mysqli_fetch_array?

EDIT: I'm getting alot of exotic queries to return something like 15,12,3,17,9,5,11...... I just want to return rows 1-20 in a column randomized. Basically I'm trying to ask in sql parlance how to get this:

 ORDER BY front_weight DESC LIMIT 0, 20

to play nice with this:

 ORDER BY RAND()

I really hope I don't need joins and pseudo-tables, but if I do, so be it.

Upvotes: 0

Views: 107

Answers (4)

user3423909
user3423909

Reputation: 189

Thanks guys for all your help. After continuing to fiddle with it some more I found the right query for my needs:

$sql_rand_limit_range = "SELECT * FROM posts 
                         WHERE active='y' 
                         AND front_weight BETWEEN 1 AND 20 
                         ORDER BY RAND()";

Good old BETWEEN, hardly used but never truly forgotten!

Upvotes: 1

Sean
Sean

Reputation: 12433

Try using INNER JOIN instead of IN

$sql_rand_limit_range = "SELECT p1.* FROM posts p1
                         INNER JOIN
                            (SELECT front_weight 
                             FROM posts ORDER BY front_weight DESC LIMIT 0, 20) as p2
                         ON p1.front_weight = p2.front_weight
                         WHERE p1.active='y' 
                         AND p1.front_weight > 0
                         ORDER BY RAND()";

According to your comments you are wanting a simplified version. Then try

$sql_rand_limit_range = "SELECT * FROM posts
                         WHERE active='y'
                         AND front_weight BETWEEN 1 AND 20
                         ORDER BY RAND()";

Upvotes: 0

Nacht Blaad
Nacht Blaad

Reputation: 415

You could try a RIGHT JOIN

SELECT posts.* FROM posts 
RIGHT JOIN 
(SELECT front_weight FROM posts WHERE active='y' AND front_weight > 0 DESC LIMIT 0,20) randomizer
ON posts.front_weight = randomizer.front_weight
ORDER BY RAND()

Upvotes: 0

Anand Somasekhar
Anand Somasekhar

Reputation: 596

Try this one

$sql_rand_limit_range = "SELECT * FROM posts 
                         WHERE active='y' 
                         AND front_weight > 0 
                         AND front_weight 
                         IN (SELECT front_weight 
                         FROM posts ORDER BY front_weight DESC) 
                         ORDER BY RAND  LIMIT 0, 20";

Upvotes: 0

Related Questions