Getting the 90th percentile of a list of values

Hi,

I need to calculate the 90th percentile of a list of values:

0.0099

0.0129

0.0031

0.0219

0.2632

0.0124

0.0493

0.05

0.0433

how would I go about the calculation? I know the answer I need to come up with is 0.0713,9. Any suggestions?

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05)
INSERT INTO @Temp VALUES(0.0433)

SELECT DATA
FROM @Temp
ORDER BY DATA ASC

--90th percentile
SELECT ((
SELECT TOP 1 DATA
FROM   (
SELECT  TOP 90 PERCENT DATA
FROM    @Temp
WHERE   DATA IS NOT NULL
ORDER BY DATA
) AS A
ORDER BY DATA DESC) +
(
SELECT TOP 1 DATA
FROM   (
SELECT  TOP 10 PERCENT DATA
FROM    @Temp
WHERE   DATA IS NOT NULL
ORDER BY DATA DESC
) AS A
ORDER BY DATA ASC)) / 2.0

May 20th, 2015 1:27am

I get different value

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05)
INSERT INTO @Temp VALUES(0.0433)

SELECT *,0.90* COUNT(*) OVER () FROM @Temp
ORDER BY DATA

May 20th, 2015 1:47am

My query currently returns 0,2632

where I need to get 0.0713,9 what am I doing wrong.

May 20th, 2015 1:50am

May 20th, 2015 3:07am

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05)
INSERT INTO @Temp VALUES(0.0433)

select max(case when rownum*1.0/numrows <= 0.9 then DATA end) as percentile_90th
from (select DATA,
row_number() over (order by DATA) as rownum,
count(*) over () as numrows
from @Temp
where DATA is not null
) t
May 20th, 2015 3:11am

SQL Server 2012

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05)
INSERT INTO @Temp VALUES(0.0433)

SELECT *,PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DATA)  OVER () AS MedianDisc
FROM @Temp