Rank analytical functions rank items in a group, compute distribution, and divide a result set into a number of groupings .Rank analytical functions include: RANK, DENSE_RANK, PERCENT_RANK, and NTILE. The inverse distribution analytical functions are PERCENTILE_CONT and PERCENTILE_DISC.
Suppose you want to rank employee salaries. In the following example, the NTILE function divides employees into four groups based on the employee’s salary. Employees whose ntile ranking = 1 are in the top 25% salary range.
SELECT Name Salary, NTILE(4) OVER(ORDER BY salary DESC)as Ranking FROM emp1;
Name |
Salary |
Ranking |
|---|---|---|
Sandy |
55,000 |
1 |
Peter |
48,000 |
1 |
Lisa |
38,000 |
1 |
Scott |
29,000 |
1 |
Tim |
29,000 |
2 |
Tom |
28,000 |
2 |
Mike |
28,000 |
2 |
Adam |
25,000 |
3 |
Antonia |
22,000 |
3 |
Jim |
22,000 |
3 |
Anna |
18,000 |
4 |
Jeff |
18,000 |
4 |
Amy |
18,000 |
4 |
NTILE is a analytical function that distributes or ranks query results into a specified number of buckets and assigns the bucket number to each row in the bucket. You can divide a result set into tenths (deciles), fourths (quartiles), and other numbers of groupings.
The rank analytical functions require an OVER (ORDER BY) clause. The ORDER BY clause specifies the parameter on which ranking is performed and the order in which the rows are sorted in each group. Note that this ORDER BY clause is used only within the OVER clause and is not an ORDER BY for the SELECT.
The OVER clause indicates that the function operates on a query result set. The result set is the rows that are returned after the FROM, WHERE, GROUP BY, and HAVING clauses have all been evaluated. The OVER clause defines the data set of the rows to include in the computation of the rank analytical function.