What is the use of the rank function in SQL?

Data Science, AI and ML
,
#1

The RANK() function is a window function in SQL that assigns a rank to each row within a group of data sets. You can choose what you’re ranking, within what group(s), and by what. These are particularly useful for answering top-N and bottom-N questions.

Here’s an example of a common syntax:

SELECT column,

RANK() OVER (PARTITION BY… ORDER BY…) as rank

FROM table;

You can choose what you’re ranking with the clause SELECT column. PARTITION BY specifies parameters to partition the result set’s rows, and ORDER BY sorts the rows in each partition.

Suppose you want to find the top 10 customers in each state that are generating the most revenue for a company. You would select the column for customers, partition by the state, and order by revenue high to low.