PERCENT

Oracle

Example

SELECT EMPLOYEE_ID, LAST_NAME, SALARY,
         PERCENT_RANK () OVER (ORDER BY SALARY DESC) "Percent Rank"
FROM   EMPLOYEES
ORDER BY SALARY DESC;

Output

EMPLOYEE_ID | LAST_NAME | SALARY   | Percent Rank
-------------|-----------|----------|-----------------
100         | King      | 24000    | 0
101         | Kochhar   | 17000    | 0.0222222222222222
102         | De Haan   | 17000    | 0.0222222222222222
103         | Hunold    | 9000     | 0.0666666666666667

Explanation

The example uses the PERCENT_RANK() function in Oracle SQL to calculate the percentile rank of each employee’s salary compared to all other salaries in the ‘EMPLOYEES’ table. The PERCENT_RANK() function computes the relative rank of a row returned by a query in the form of a percentage between 0 and 1. The highest salary has a percent rank of 0, and all other salaries are ranked accordingly.

SQL Server

Example

SELECT TOP 10 PERCENT *
FROM Products
ORDER BY ProductName;

Output

ProductId | ProductName | SupplierId | CategoryId | QuantityPerUnit | UnitPrice
-------------------------------------------------------------------------------
 1        | Chai        | 1          | 1          | 10 boxes x 20 bags | 18
 2        | Chocolade   | 2          | 2          | 24 - 12 oz bottles | 19
 3        | Konbu       | 3          | 3          | 12 - 550 ml bottles | 30
 4        | Geitost     | 4          | 4          | 20 - 1 kg bags | 21
 5        | Guaraná Fantástica | 4 | 4 | 12 - 355 ml cans | 4.5

Explanation

The SQL code showcases the PERCENT keyword’s function. It retrieves the top 10 percent of records from the Products table, ordering them by ProductName. The returned records are a subset, making up 10 percent of the total number, determined based on the specified ProductName ordering.