How to find Nth percentile with SQLite?

percentile, sqlite

Solution

sqlite is not strong in analytical processing but if your data is not very large, you can try to emulate percentile with `ORDER BY`, `LIMIT 1` and a calculated `OFFSET`. Note that `OFFSET` is zero-based so you need to adjust it by one.

SELECT
  height AS 'male 90% height'
FROM table
WHERE gender='male'
ORDER BY height ASC
LIMIT 1
OFFSET (SELECT
         COUNT(*)
        FROM table
        WHERE gender='male') * 9 / 10 - 1;

Problem

I'll like to find Nth percentile. for example: table: htwt; columns: name, gender, height, weight result: ``` | gender | 90% height | 90% weight | | male | 190 | 90 | | female | 180 | 80 | ```

Original source