Mathematical Function used in Window Function

Introduction

Rank():
- The RANK() window function is used in SQL to assign a unique rank to each distinct row within a result set based on the specified ordering. It's often used for ranking rows in a dataset based on certain criteria.
- Rows with the same values that determine the ranking will receive the same rank, and the next rank will be skipped.

Here's the basic syntax of the RANK() function:

    RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)

PARTITION BY partition_expression: This clause is optional and allows you to divide the result set into partitions. The RANK() function will rank rows separately within each partition. If omitted, the ranking will be based on the entire result set as a single partition.
ORDER BY sort_expression: Specifies the column or columns by which the rows should be ordered for ranking.

Here's an example to illustrate the usage of the RANK() function:

Suppose you have a table named "scores" with columns "student_id" and "score", and you want to rank students based on their scores:

    SELECT
        student_id,
        score,
        RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM
        scores;

In this query:

The ORDER BY score DESC clause sorts the rows based on the "score" column in descending order.
The RANK() function assigns a unique rank to each row based on the specified ordering.
While using rank() we must do sorting by using the order by clause

Dense Rank()
- The DENSE_RANK() window function in SQL is used to assign a unique rank to each distinct row within a result set based on the specified ordering. The main difference between DENSE_RANK() and the regular RANK() function is that DENSE_RANK() does not skip ranks in case of tied values.
- Rows with the same values determining the ranking will receive the same rank, and the next rank will not be skipped.
  
  Here's the basic syntax of the DENSE_RANK() function:
```
  DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)
```
  - PARTITION BY partition_expression: This clause is optional and allows you to divide the result set into partitions. The DENSE_RANK() function will rank rows separately within each partition. If omitted, the ranking will be based on the entire result set as a single partition.
  - ORDER BY sort_expression: Specifies the column or columns by which the rows should be ordered for ranking.

        SELECT
            student_id,
            score,
            DENSE_RANK() OVER (ORDER BY score DESC) AS dense_score_rank
        FROM
            scores;

In this query:

The ORDER BY score DESC clause sorts the rows based on the "score" column in descending order.
The DENSE_RANK() function assigns a unique rank to each row based on the specified ordering. It doesn't skip ranks for tied values, so if multiple students have the same score, they will receive the same rank.

The result set will show each student's ID, their score, and the dense rank assigned to their score. Tied scores will result in the same dense rank being assigned to the tied rows, without skipping any ranks.

Remember that the behavior of DENSE_RANK() is different from the regular RANK() function, which does skip ranks for tied values. The choice between the two functions depends on whether you want to account for tied values in the ranking or not.

RANK VS DENSE RANK:
- The only difference between rank and dense rank is that when we have two value with the same rank
- In the rank function, both of them will be allotted the same rank and the next rank will be skipped
- In dense rank, both of them will be given separate ranking

Mathematical Function used in Window Function

Table of contents

Introduction