I've had a look through the archives and cannot see a solution to this. I am wondering if there is a nicer way to do the following.
I have a table where one of the columns contains a time period for an activity that I am logging. I need to grab the 95th percentile, when ordered by duration. I have two solutions so far, but processing time is important. Solution 1: Use perl to drag the (relevant) rows into an array, sorted by duration (either by the sql box, or by sorting the array), and then count the number of elements, and grab the (number of elements * .95) element. I moved away from this, to the following, as I don't want to pull every row to the client (there are millions of rows in the table)..: select count(*) from percentile where <criteria>; Work out 95% or this value. create temporary table percentile (id int unsigned auto_increment primary key, scantime decimal(20,10)); insert into percentile (scantime) select processingtime from emails where <same criteria> order by processingtime desc limit <value from above>; select min(scantime) from percentile; This appears to be quicker, aswell as saving me pulling all the rows. I don't need to pull all the rows, as the other calculations I need to do on the data specified by <criteria> is easily accomplished by a sum(processingtime) and avg(processingtime) -- I guess I was basically looking for perc(processingtime,95) or something, but don't appear to be able to fine it! Thanks in advance for any help/suggestions you may have. Karl ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs SkyScan service. For more information on a proactive anti-virus service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
