ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() in sql
In SQL database management systems, built-in database window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() are used to rank or number table rows in database table result output based on some particular conditions or a specific order. These database functions are typically used when database users need to arrange or group table data in a particular order and apply table operations such as ranking a database table, numbering table data in sequence, or dividing table data into groups and displaying them.
The ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions are pre-defined in the built-in window function family and are often used with the OVER() clause, which displays the current window or partition information on which these database functions operate.
ROW_NUMBER() in SQL database management.
The ROW_NUMBER() function in SQL databases provides integer number values to table rows in a unique sequential order in the output, starting with 1 for the first row in the displayed table data. User-defined table numbering here depends on the table order defined in the ORDER BY clause.
Syntax of ROW_NUMBER() in SQL.
SELECT column_name,
ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
FROM table_name;
Example of ROW_NUMBER() in SQL.
- Suppose a database user has a database table named EMPLOYEES, which already has the table fields employee_id, emp_name, and salary column fields defined.
- The database user wants to assign each employee a unique row number based on their salary.
SELECT employee_id, emp_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employee;
ROW_NUMBER() in SQL Explanation.
- The ROW_NUMBER() function assigns a unique row number to each employee in the EMPLOYEE table, sorting them in descending order by salary.
- Even if two employees have the same salary, the ROW_NUMBER() function assigns them different row numbers.
RANK() in SQL database management.
The RANK() function in SQL databases assigns a rank number to each table row in the result output. However, if a tie occurs in the ORDER BY clause, where table rows with identical values are present, RANK() adds up the gaps in the ranking. The same rank is assigned to the tied table rows. But the next rank is ignored.
Syntax of RANK() in SQL database.
SELECT column_name,
RANK() OVER (ORDER BY column_name) AS rank
FROM table_name;
Example of RANK() in SQL database.
- Here, using the EMPLOYEE table, ranking is provided by ranking the employee data based on their salary. There is a possibility of a tie in the same field row of the EMPLOYEE table.
SELECT employee_id, emp_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employee;
RANK() in SQL database explanation.
- Here, if the salary of two employees in the EMPLOYEE table is the same, then both employees are provided the same rank. However, the next employee is assigned a rank after a gap. For example, if two employees have the rank of 1, the next rank will be 3, not 2.
- For example, if there are two highest-paid employees in the Employee table, both employees are assigned rank 1, and the next employee is assigned rank 3. Rank 2 is omitted in the table.
DENSE_RANK() in SQL database management.
The DENSE_RANK() function is a similar database function to RANK() in SQL database tables, but the DENSE_RANK() function does not provide a gap in ranking. If two table rows in the Employee database table have the same value, they are assigned the same rank, and the next table row is assigned the next rank without skipping any numbers.
Syntax of DENSE_RANK() in SQL database.
SELECT column_name,
DENSE_RANK() OVER (ORDER BY column_name) AS dense_rank
FROM table_name;
Example of DENSE_RANK() in SQL database.
- It uses the EMPLOYEE table to rank employees according to their salary and display them sequentially without any specific gap.
SELECT employee_id, emp_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employee;
DENSE_RANK() in SQL database explanation.
- Here, employees earning the same salary in the EMPLOYEE table are assigned the same rank.
- Where there is no gap in the employee ranking order. For example, if two employees have a rank of 1, the next employee will have a rank of 2, not 3, as defined in RANK().
NTILE() in SQL database management.
The NTILE() function in SQL databases displays the table row result output by dividing it into a fixed number of equal parts, or “tiles.” It displays the table group or tile number to which each table row belongs. The NTILE() function is useful for dividing table data into quartiles, deciles, or any other partition.
The syntax of NTILE() in SQL databases is.
SELECT column_name,
NTILE(number_of_tiles) OVER (ORDER BY column_name) AS tile
FROM table_name;
Element of NTILE() in SQL databases.
- Number_of_tiles represents the number of table groups, or “tiles,” into which the table result output group is divided.
- The table result set is ordered or sequenced within the table using the ORDER BY clause.
Example of NTILE() in SQL database.
- If the database user wants to divide the Employee table into 3 quartiles based on their salary.
SELECT employee_id, emp_name, salary,
NTILE(3) OVER (ORDER BY salary DESC) AS quartile
FROM employee;
Explanation of NTILE() in SQL database.
- Here, the employees in the Employee table are divided into 3 equal groups (quartiles) based on their salary.
- The highest-paid employees will be displayed in the first quartile, and the lowest-paid employees will be added in the last quartile.
- If the total number of Employee table rows is not divisible by 3, some tiles will have one more table row than others, to balance the number of rows across all tiles.
Main Difference Between Row_Number(), Rank(), Dense_Rank(), And Ntile()
| Database Function | Table Function Description | Handling of Ties | Use Case Example |
| ROW_NUMBER() function | Row_number() function used to Assigns a unique, sequential number to each row in database table. | It supports No ties, all rows are numbered distinctly from each other. | It used to Creating a unique identifier or pagination in proper sequence. |
| RANK() function | Rank() used to Assigns ranks to table rows, but introduces gaps in the ranking if there are ties between same table row value. | It supports Tied rows get the same rank, but subsequent ranks are skipped during numbering process. | It used to Ranking table row items where gaps in ranks are acceptable according. |
| DENSE_RANK() function | Dense_rank() used to Assigns ranks without table row gaps even when there are ties between same table row. | Here it shows Tied rows get the same rank, but subsequent ranks are not skipped during process. | It used to Ranking items where no gaps in ranks are desired result. |
| NTILE()function | Ntile() used to Divides the result set into a specified number of approximately equal parts tiles with table row. | it Does not handle ties directly in database table row, here table rows are divided into tiles group. | Here it Dividing table data into groups for analysis or multiple parts. (ex, quartiles, deciles). |
Summary of ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() functions.
- The ROW_NUMBER() function in a SQL database table is useful when the database user needs to display a unique sequential number order for each table row.
- The RANK() function in a database table is important when the database user needs to rank table row items with gaps between tie ranks.
- DENSE_RANK() is applied in a database table when the database user needs to rank table data without gaps between tie ranks.
- NTILE() in a database table is applied when the database user needs to display table data by dividing it into specific number parts, such as quartiles, deciles, or other percentiles.
