The *DENSE_RANK()* function in SQL is used to assign a rank to each row within a result set partitioned by a specified column and ordered by a specified criterion. It assigns ranks in such a way that there are no gaps in the ranking sequence when there are ties (i.e., rows with the same values in the specified order).
Here’s how to use the *DENSE_RANK()* function step-by-step:
1. **Basic Syntax**:
The basic syntax for using the *DENSE_RANK()* function is as follows:
DENSE_RANK() OVER (PARTITION BY column1, column2 ORDER BY column3)

2. **Components**:
- **PARTITION BY**: This clause divides the result set into partitions to which the *DENSE_RANK()* function is applied. It’s optional; if not specified, the function treats the entire result set as a single partition.
- **ORDER BY**: This clause determines the order of the rows within each partition. It is required.
3. **Example**:
Assume we have a table called *Employees* with the following structure:
CREATE TABLE Employees (
EmployeeID INT,
DepartmentID INT,
Salary DECIMAL(10, 2)
);

If we want to assign a dense rank to each employee within each department based on their salary, we can write the following query:
SELECT
EmployeeID,
DepartmentID,
Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DenseRank
FROM
Employees
ORDER BY
DepartmentID, DenseRank;

4. **Explanation of the Example**:
- This query selects the *EmployeeID*, *DepartmentID*, and *Salary* from the *Employees* table.
- It computes a dense rank (*DenseRank*) based on the *Salary*, ordered in descending order, but resets for each *DepartmentID*.
- The result set will be ordered by *DepartmentID* first, then by the dense rank.
5. **Output**:
The output will look like this (assuming the relevant data exists):
| EmployeeID | DepartmentID | Salary | DenseRank |
|------------|--------------|---------|-----------|
| 101 | 1 | 70000.00| 1 |
| 102 | 1 | 50000.00| 2 |
| 103 | 1 | 50000.00| 2 |
| 104 | 2 | 60000.00| 1 |
| 105 | 2 | 40000.00| 2 |

In this output, you can see that for DepartmentID 1, both employee 102 and 103 have the same salary, and therefore they receive the same dense rank (2), but there is no gap as rank 1 is assigned to the highest salary. For DepartmentID 2, rank 1 is given to the highest salary (employee 104), and rank 2 is assigned to employee 105 with a lower salary.