How to find Nth percentile with SQLite?

Question

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 |

Answer 1

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
  COUNT(*) AS male_count
  FROM table
  WHERE gender='male';

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