In SQL, the RANK and DENSE_RANK functions are used to assign a rank to each row within a partition of a result set based on the values of one or more specified columns. Both functions are often utilized in analytical queries to provide rankings based on specific criteria.
Differences Between RANK and DENSE_RANK
1. RANK: This function assigns a rank to each row starting from 1. If there are ties (i.e., rows with the same values), the same rank will be assigned to those rows, but the next rank value will be skipped. For example, if two rows are tied for rank 1, the next rank will be 3.
2. DENSE_RANK: This function also assigns a rank to each row, but unlike RANK, it does not skip any ranks. If there is a tie, the same rank is assigned to the tied rows, and the next rank will be the immediate next integer. For example, if two rows are tied for rank 1, the next rank will be 2.
Basic Syntax
The syntax for using these functions generally looks like this:
RANK() OVER (PARTITION BY <column1>, <column2> ... ORDER BY <order_column>)
DENSE_RANK() OVER (PARTITION BY <column1>, <column2> ... ORDER BY <order_column>)
- PARTITION BY: This clause divides the result set into partitions to which the function is applied.
- ORDER BY: This clause defines the order in which the ranks are assigned within each partition.
Example Usage
Let's say you have a table called scores that contains the columns student_id, subject, and score. You want to rank students by their scores within each subject.
SELECT
student_id,
subject,
score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank
FROM
scores
ORDER BY
subject, rank;
Explanation of the Example:
1. Partitioning: The results are partitioned by subject. This means that the ranking will reset for every subject.
2. Ordering: The scores are ordered in descending order, so the highest score gets the highest rank (i.e., rank 1).
3. RANK: If two students score the same in a subject, both will receive the same rank, and the next rank will be incremented by the number of tied students.
4. DENSE_RANK: If two students have the same score, both will still receive the same rank, but the subsequent rank number will not skip.
Example Output
Assuming the following data in scores:
| student_id | subject | score |
|------------|---------|-------|
| 1 | Math | 90 |
| 2 | Math | 90 |
| 3 | Math | 80 |
| 4 | Science | 85 |
| 5 | Science | 85 |
| 6 | Science | 80 |
The output of the above query would be:
| student_id | subject | score | rank | dense_rank |
|------------|---------|-------|------|------------|
| 1 | Math | 90 | 1 | 1 |
| 2 | Math | 90 | 1 | 1 |
| 3 | Math | 80 | 3 | 2 |
| 4 | Science | 85 | 1 | 1 |
| 5 | Science | 85 | 1 | 1 |
| 6 | Science | 80 | 3 | 2 |
In this example:
- The students in Math with scores 90 both receive rank 1, and the next student with a score of 80 receives a rank of 3 (due to the tie).
- In Science, the same logic applies with DENSE_RANK, where students with a score of 85 both receive rank 1, and the next rank goes to 2 (for the student with 80).
This demonstrates how RANK and DENSE_RANK can be effectively utilized to analyze and categorize data in SQL based on specific criteria.