Pull 1 Random Entry From My The Top 10 Entries

[sintax63]

I have a query that pulls my top 10 most clicked on entries. The code is as follows:

 

Select * FROM clicks WHERE title!='' ORDER BY (clicks + 1000) DESC LIMIT 10

 

The reason I'm using (clicks + 1000) is because an entry with 4 clicks gets listed before one with 29. I'm sure there is a way around that but haven't figured that one out yet either. Now, getting back to the point of this topic, I would like to take those top 10 entries from the query above, and select one entry at random to show.

 

SELECT c.name, c.title, c.clicks, s.name, s.title, s.description, s.synonym, s.dept FROM clicks c INNER JOIN schedule s ON substring(c.name,1,5) = substring(s.name,1,5) WHERE c.title!='' ORDER BY (RAND() * (c.clicks + 1000)) LIMIT 1

 

Sadly, this is pulling a random entry from all of my entries, not just one from the top 10. Any ideas?

 

Thanks!

[Jessica]

You should probably fix the sorting issue first. Is the column a varchar instead of an int?

 

You'll probably need to use a subquery for the random part. Or you could select a random number in PHP first, 

$row = rand(0,9);
$sql = "Select * FROM clicks WHERE title!='' ORDER BY clicks DESC LIMIT $row, 1";

Edited February 25, 2013 by Jessica

[shlumph]

The reason I'm using (clicks + 1000) is because an entry with 4 clicks gets listed before one with 29.

That's not good, what is the data type? It should be an integer, sounds like it might be a varchar.

 

 

Here's a quick and dirty solution:

select * from (select * from clicks order by clicks desc limit 10) as top10clicks order by rand() limit 1

[sintax63]

Yeah, for some reason it was set up as a VARCHAR and I didn't notice it. After I got that working (thanks Jessica and shlumph) everything else fell into place.

 

Thanks again!