{"id":1737,"date":"2018-09-08T17:19:34","date_gmt":"2018-09-09T00:19:34","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1737"},"modified":"2025-01-17T01:38:36","modified_gmt":"2025-01-17T08:38:36","slug":"sql-percent_rank","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-percent_rank\/","title":{"rendered":"SQL PERCENT_RANK Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL PERCENT_RANK()<\/code> function to calculate the percentile rankings of rows in a result set.<\/p>\n\n\n\n

The PERCENT_RANK()<\/code> is a window function<\/a> that calculates the percentile ranking of rows in a result set.<\/p>\n\n\n\n

The syntax of the PERCENT_RANK()<\/code> function is as follows:<\/p>\n\n\n

PERCENT_RANK()  \n    OVER ( \n        PARTITION BY expr1, expr2,...\n        ORDER BY expr1 [ASC|DESC], expr2 ...\n    )<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The PERCENT_RANK()<\/code> function returns a percentile ranking number which ranges from zero to one.<\/p>\n\n\n\n
For a specific row, PERCENT_RANK()<\/code> uses the following formula to calculate the percentile rank:<\/p>\n\n\n
(rank - 1) \/ (total_rows - 1)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this formula, rank<\/code> is the rank of the row. total_rows<\/code> is the number of rows that are being evaluated.<\/p>\n\n\n\n
Based on this formula, the PERCENT_RANK()<\/code> function always returns zero for the first row the result set.<\/p>\n\n\n\n
The PARTITION BY<\/code> clause divides the rows into partitions and the ORDER BY<\/code> clause specifies the logical order of rows for each partition. The PERCENT_RANK()<\/code> function is calculated for each ordered partition independently.<\/p>\n\n\n\n
The PARTITION BY<\/code> clause is optional. If you omit the PARTITION BY<\/code> clause, the function treats the whole result set as a single partition.<\/p>\n\n\n\n
SQL PERCENT_RANK() function examples #<\/a><\/h2>\n\n\n\n
We will use the employees<\/code> and departments<\/code> tables from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
Using SQL PERCENT_RANK() function over the query result set example #<\/a><\/h3>\n\n\n\n
The following query finds the percentile ranks of employees by their salaries:<\/p>\n\n\n
SELECT<\/span>\n    first_name,\n    last_name,\n    salary,\n    ROUND<\/span>(\n        PERCENT_RANK<\/span>() OVER<\/span> (\n            ORDER<\/span> BY<\/span> salary\n        ) \n    ,2<\/span>) percentile_rank\nFROM<\/span>\n    employees;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The ORDER BY<\/code> clause sorted the salaries of employees and the PERCENT_RANK()<\/code> function calculated the percentile ranking of employees by salaries in ascending order.<\/p>\n\n\n\n
Let’s analyze some rows in the output.<\/p>\n\n\n\n
In this example, we omitted the PARTITION BY<\/code> clause so the function treated the whole employees in the result set as a single partition. Notice that we used the ROUND()<\/a><\/code> function to round the percentile rankings to two decimal places.<\/p>\n\n\n\n