MySQL Forums
Forum List  »  Perl

Re: mysql + php (percentile)
Posted by: Rick James
Date: August 30, 2008 03:24PM

1. Create another table with an auto_increment plus whatever you want to display.

2. INSERT into that table SELECT ... FROM existing table ORDER BY .... (Don't include the auto_increment field, or claim it is NULL.

You now have a table with the rows numbered 1..368.

3. SELECT @tot = COUNT(*) to get the '368'

4. SELECT ... WHERE id > 0.10 * @tot LIMIT 1; -- gets 10th percentile item.
(Yeah, there are end cases; should you get #36 or #37 in this case. I'll leave details like that as an exercise to the reader.)

There are also elegant ways use and assign variables (SELECT @xx := @xx+1, ...) to do the job in a single SELECT.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.