What are some examples of how to use the RANK function?

RANK is a window function in SQL that allows you to assign a rank to each row within a result set based on the values of a specified column.

Here are some examples of how to use the RANK function:

Basic RANK function:

Download
Copy code
SELECT student_id, test_score,
RANK() OVER (ORDER BY test_score DESC) AS score_rank
FROM test_results;

This query assigns a rank to each student based on their test score. The highest score will be ranked 1, the second highest will be ranked 2, and so on.

RANK with PARTITION:

Download
Copy code
SELECT class_id, student_id, test_score,
RANK() OVER (PARTITION BY class_id ORDER BY test_score DESC) AS class_rank
FROM test_results;

In this query, we assign a rank to each student within their class. Each class is considered a separate partition and is ranked independently of the other classes.

RANK with multiple columns in ORDER BY:

Download
Copy code
SELECT student_id, test_score, study_hours,
RANK() OVER (ORDER BY test_score DESC, study_hours DESC) AS performance_rank
FROM test_results;
This query assigns a rank to each student based on both their test score and study hours. The highest score and longest study hours will be ranked 1, the second highest and second longest will be ranked 2, and so on.

RANK with DENSE_RANK:

Download
Copy code
SELECT student_id, test_score,
DENSE_RANK() OVER (ORDER BY test_score DESC) AS dense_score_rank
FROM test_results;
The DENSE_RANK function behaves similarly to the RANK function, but it does not skip any ranks when multiple students have the same test score. For example, if there are three students with the same highest test score, they will all be ranked 1 using the DENSE_RANK function, whereas the RANK function would assign them ranks 1, 2, and 3, respectively.

About Author

SURF

See author's posts