MySql Percentile works in Select statement but not Function - ?

[Jason Wisdom]

Hi everybody,
I am trying to calculate Percentile of values.
Basically, the SELECT statement works fine, scoring Testid=1 at the 60th percentile where it should be. But the FUNCTION with exact same select statement, always gives 100th percentile.
Any idea why?
Thank you,
Jason


--


My SELECT statement works fine:

select
dt.readingid, round(100*(ct.cnt-rank+1)/ct.cnt,0) as totalchempct
from
(SELECT
rc.readingid,
@curRank := @curRank + 1 AS rank
FROM (select readingid, sum(weight*chemvalue) as chemistry from readingchem where chemdimid=1 group by readingid) rc,
(SELECT @curRank := 0) r
ORDER BY rc.chemistry desc) as dt,
(select count(distinct readingid) as cnt from readingchem) as ct
where dt.readingid=1;

I get back "60", as reading #1's test results are the 60th percentile of all results.

But the FUNCTION, with this same pasted query, always returns "100" and never "60":

--

DELIMITER $$
CREATE FUNCTION `fngetchemdimpct`(chemdimid int, readingid int) RETURNS decimal(10,4)
BEGIN

declare chempct decimal(10,4);

SET chempct = (
select
round(100*(ct.cnt-rank+1)/ct.cnt,0)
from
(SELECT
rc.readingid,
@curRank := @curRank + 1 AS rank
FROM (select readingid, sum(weight*chemvalue) as chemistry from readingchem where chemdimid=1 group by readingid) rc,
(SELECT @curRank := 0) r
ORDER BY rc.chemistry desc) as dt,
(select count(distinct readingid) as cnt from readingchem) as ct
where dt.readingid=1);

RETURN chempct;
END$$
DELIMITER ;