Select top 10%?

[imstillatwork]

Hello!

Can I get a random single row withing the top 10% of a numeric field in a single query??

I was thinking something like

select name, value
from table
where value > (
select min(value) FROM table order by value DESC LIMIT 10 - but I want 10%, not 10 rows
)
order rand()
LIMIT 1


What to do?



Kevin

http://www.lot-o-nothin.com/cfml/

 

[imstillatwork]

Just to be sure I am clear, if I just used limit 10, then those top ten rows could be all the same value, or include values lower then the top 10% if the table was small. Whereas the top 10% highest values could actually be any number of rows.



Kevin

http://www.lot-o-nothin.com/cfml/

 

[TonyGroves]

To do it in a single query, you would have to include at some stage a LIMIT clause where the limit value is the result of a COUNT, but it appears that can't be done, the LIMIT value must be a constant.

 

[imstillatwork]

I'll just pick on random row from the top N DISTINCT values than. They end users will never know the difference - and that's what matters, right?

Kevin

http://www.lot-o-nothin.com/cfml/

 

[OsakaWebbie]

Whether it matters if you do it right or not, only you can answer, but if you're still looking for a solution, here's my thought: If you are processing this data in some other code, e.g. PHP, you could do just a single query for all the records (still with "ORDER BY value DESC"), then in code, knowing the number of rows returned, just randomly pick a number between 1 and 10% of the number of rows, and work with that row.

 

[imstillatwork]

thanks, I've worked something out similar to that

Kevin

http://www.lot-o-nothin.com/cfml/