Understanding SQL Ranking Functions: RANK, DENSE_RANK, and ROW_NUMBER

Saiyam Jain — Thu, 25 Jul 2024 14:25:36 +0000

In SQL, ranking functions are used to assign a rank or a sequential number to rows in a result set based on the values of one or more columns. These functions are particularly useful for tasks like sorting, ranking, and partitioning data. The most commonly used ranking functions are RANK, DENSE_RANK, and ROW_NUMBER. This article explains these functions and demonstrates how to use them with SQL queries.

Key Concepts

RANK()
- Description: Assigns a rank to each row within a result set based on the specified ordering. Rows with the same values receive the same rank, and subsequent ranks are adjusted to account for ties, resulting in gaps in the ranking sequence.
- Example: If two rows share the highest rank (1), the next rank assigned will be 3, leaving a gap.
DENSE_RANK()
- Description: Similar to RANK, but without gaps in the ranking values. Rows with the same values receive the same rank, and the next distinct value gets the immediate next rank.
- Example: If two rows share the highest rank (1), the next rank assigned will be 2, without any gaps.
ROW_NUMBER()
- Description: Assigns a unique sequential integer to each row based on the specified ordering. It does not consider the values of the rows for ranking and does not create ties or gaps.
- Example: Each row is given a unique number starting from 1, regardless of the values in the rows.

Practical Example

Consider a table named StudentMarks with the following data:

Name	Marks
A	100
B	100
C	89

To apply these ranking functions, we need to order the data by the Marks column in descending order.

SQL Queries

Here’s how you can use RANK, DENSE_RANK, and ROW_NUMBER functions in SQL to get the ranking of each row:

1. `DENSE_RANK`

SELECT
    Name,
    Marks,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRank
FROM
    StudentMarks;

Output:
| Name | Marks | DenseRank |
|------|-------|-----------|
| A | 100 | 1 |
| B | 100 | 1 |
| C | 89 | 2 |

Explanation: Both A and B have the highest marks (100) and share rank 1. The next distinct mark (89) gets rank 2.

2. `RANK`

SELECT
    Name,
    Marks,
    RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM
    StudentMarks;

Output:
| Name | Marks | Rank |
|------|-------|------|
| A | 100 | 1 |
| B | 100 | 1 |
| C | 89 | 3 |

Explanation: Both A and B share rank 1. The next distinct mark (89) receives rank 3, creating a gap because ranks 1 and 2 are taken.

3. `ROW_NUMBER`

SELECT
    Name,
    Marks,
    ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNumber
FROM
    StudentMarks;

Output:
| Name | Marks | RowNumber |
|------|-------|-----------|
| A | 100 | 1 |
| B | 100 | 2 |
| C | 89 | 3 |

Explanation: Each row is assigned a unique sequential number based on the descending order of marks. There are no ties or gaps.

Conclusion

SQL ranking functions such as RANK, DENSE_RANK, and ROW_NUMBER are powerful tools for organizing and analyzing data. Understanding the differences between these functions allows you to choose the appropriate ranking method based on your needs, whether it’s handling ties, avoiding gaps, or simply numbering rows sequentially. By using these functions effectively, you can gain valuable insights and present data in a meaningful way.

5 ways to reverse a string in Python

Saiyam Jain — Sun, 07 Nov 2021 19:10:30 +0000

Hello everyone, in this post I will provide you 5 different ways to reverse a string using python programming.

1

Using Slicing

def StrRev(string):
    string = string[::-1]
    return string

s = input("Enter a string: ")

print (StrRev(s))

2

Using Reversed Function

def StrRev(string):
    string = "".join(reversed(string))
    return string

s = input("Enter a string: ")
print (StrRev(s))

3

Using Loop

def StrRev(s):
  strg = ""
  for i in s:
    strg = i + strg
  return strg

s = input("Enter a string: ")
print (StrRev(s))

4

Using Recursive function

def StrRev(s):
    if len(s) == 0:
        return s
    else:
        return StrRev(s[1:]) + s[0]

s = input("Enter a string: ")
print(StrRev(s))

5

Using Stack Operations

def StackCreate():
    stack=[]
    return stack


def isEmpty(stack):
    if len(stack) == 0:
        return true


def push(stack,item):
    stack.append(item)

def pop(stack):
    if isEmpty(stack): return
    return stack.pop()


def StrRev(string):
    n = len(string)


    stack = StackCreate()

    for i in range(0,n,1):
        push(stack,string[i])

    string=""

    for i in range(0,n,1):
        string+=pop(stack)

    return string

s = input("Enter a string: ")
print (StrRev(s))

Forem: Saiyam Jain