Learn sql

**SQL RANK() Function** ======================= The `RANK()` function in SQL is a window function that assigns a ranking to each row within a result set partition, based on a specific column or expression. The ranking is done in descending order by default. **Syntax** ```sql RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column1, column2, ... ) ``` **How it Works** ---------------- 1. The `PARTITION BY` clause divides the result set into partitions based on one or more columns. 2. The `ORDER BY` clause sorts the rows within each partition in descending order. 3. The `RANK()` function assigns a ranking to each row within each partition, based on the sorted order. **Example** ```sql SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) as salary_rank FROM employees; ``` This query ranks employees by their salary in descending order. **Output** ```markdown +---------------+--------+-------------+ | employee_name | salary | salary_rank | +---------------+--------+-------------+ | John | 100000 | 1 | | Jane | 90000 | 2 | | Bob | 80000 | 3 | | Alice | 70000 | 4 | | Mike | 60000 | 5 | +---------------+--------+-------------+ ``` **Use Cases** ------------- 1. **Ranking sales performance**: Rank sales representatives by their sales amount in descending order. 2. **Identifying top performers**: Rank employees by their performance metrics, such as customer satisfaction or productivity. 3. **Analyzing student grades**: Rank students by their grades in descending order, to identify top performers. **Variations** -------------- * `DENSE_RANK()`: similar to `RANK()`, but does not skip ranking numbers if there are ties. * `ROW_NUMBER()`: assigns a unique number to each row within a partition, without considering ties. **Best Practices** ------------------ * Use `RANK()` and `DENSE_RANK()` judiciously, as they can affect query performance. * Consider using `ROW_NUMBER()` when you need to assign a unique identifier to each row.

Search

Featured matches

Other tools

Related Tasks

Go to section

Search

Learn sql

Featured matches

Other tools

Related Tasks

Help

People also viewed

Feedback and Incident Report

AI Options

Create AI Tools

Mini Tool

Vibe code an AI Tool