SQL: find percentiles

Question

I have a table that has a specific numeric column with a name Score. I would like to execute a query in this table, the result of which will contain 100 rows, each of which will represent an estimate corresponding to this percentile. For example, the result might look like this:

 Percentile | Score
---------------------
 01         | 10
 02         | 12
 03         | 12
 04         | 17
 ...        | ...
 99         | 1684
 100        | 1685

The valuation values ​​in the above result table are actual valuation values ​​that exist in the original table and are not interpolated. An interpolated result will be better, but not a requirement.

There may be several heuristics that can lead to this result. What I use today (in the code) is basically the following: the Score value corresponding to the percentile will be the rating value for which: the number of lines with lower values ​​divided by the total number of lines rounded to an integer equals the percentile (I hope that is clear)

I can consider other heuristics if they are easier to implement

I work in MS-SQL, but I would appreciate a solution that also works on MySQL.

What is the best way to achieve this?

Answer 1

In SQL Server:

SELECT  percentile, score
FROM    (
        SELECT  ROW_NUMBER() OVER (PARTITION BY percentile ORDER BY score) AS rn, percentile, score
        FROM    (
                SELECT  score, NTILE(100) OVER (ORDER BY score) AS percentile
                FROM    mytable
                ) q
        ) q2
WHERE   rn = 1