Have you ever heard about the ranking of SQL tables? What are the different ways in which we can rank the tables? In this article, we will see what ranking means in SQL and how it is achieved.

We will see the RANK and DENSE_RANK functions in detail. These are the functions used for ranking in SQL. We will also see what are the differences between these two and how we can use them in different ways. Additionally, we will also see the use case of these functions.

The RANK and DENSE_RANK functions have some similarities but the main difference between RANK and DENSE_RANK is RANK may skip numbers when the same value occurs multiple times and does not return consecutive numbers. for example - 1,2,2,4 and 5. Whereas DENSE_RANK does not skip the same value and returns the consecutive numbers for example - 1,2,2,3,4 and 5

Need of Ranking

Suppose a student is given a dataset with information regarding the exam of 50 students. It would take them some time to determine the top performers. Sorting through all that information is part of the work, which is problematic. But you can solve this issue using Sql queries like RANK and DENSE_RANK.

Operations that used to take a long time to complete can now be finished in a few seconds. These functions are used to order and assign numerical values when they fall into two entities. Both these functions are always used with the OVER() clause.

For example, a company has five employees with five different salary amounts.

Amounts are like 50 thousand, 45 thousand, 55 thousand, 35 thousand and 42 thousand respectively. Using this function, we got a rank and estimated who received the highest salary.

We will learn how to use these functions with real-time examples. Then we will understand what the critical difference between both functions is.

What is the RANK Function in SQL?

The rank function is an SQL function specifically used to calculate each row's ranking depending on the defined attributes and clauses.

Some essential points need to be considered while using the RANK function:

The use of the ORDER BY clause is necessary for using the RANK function.

PARTITION BY clause can be optional.

If two records share identical numerical values- they will also share a similar ranking value.

This leads to combined results that do not follow any sequential order, leading to irregularity within some values.

Syntax:

SELECT col_name
RANK() OVER(
[PARTITION BY exp] // if you want to partition in group
ORDER BY exp [ASC | DESC], [{exp1…}]
AS ‘r’ FROM table_name

Examples of RANK Function

Let’s see an example, to understand the RANK function better. Let’s consider Score table which has attributes like name, subject and marks.

Id

name

subject

marks

1

Ninja_1

Maths

85

2

Ninja_2

Maths

50

3

Ninja_3

Science

70

4

Ninja_4

Economics

85

5

Ninja_5

Maths

20

6

Ninja_6

English

92

7

Ninja_7

English

92

8

Ninja_8

Science

89

9

Ninja_9

Maths

63

The above table (score) contains six rows and four columns; now, we will use the RANK function to estimate the students' Rank without using the PARTITION function.

Estimating Rank without using Partition Function

SELECT
name, subject, marks,
RANK() OVER (
ORDER BY marks ASC ) AS rank
FROM Result;

Output:

name

subject

marks

rank

Ninja_5

Maths

20

1

Ninja_2

Maths

50

2

Ninja_9

Maths

63

3

Ninja_3

Science

70

4

Ninja_1

Maths

85

5

Ninja_4

Economics

85

5

Ninja_8

Science

89

7

Ninja_6

English

92

8

Ninja_7

English

92

8

We can observe that Ninja_1 and Ninja_4 are assigned the same Rank as 5, and Ninja_8, which has different marks, is assigned a new seven instead of 6.

We will implement the RANK function using the Partition clause in descending order.

Estimating Rank using Partition Function

SELECT
name, subject, marks,
RANK() OVER (
PARTITION BY subject
ORDER BY marks ASC ) AS rank
From Result;

Output:

name

subject

marks

rank

Ninja_4

Economics

85

1

Ninja_6

English

92

1

Ninja_7

English

92

1

Ninja_5

Maths

20

1

Ninja_2

Maths

50

2

Ninja_9

Maths

63

3

Ninja_1

Maths

85

4

Ninja_3

Science

70

1

Ninja_8

Science

89

2

We observe that using the PARTITION clause, we can assign ranks based on groups or partitions of data. Then the PARTITION function can help divide the resulting set into smaller groups or sections. In the above case, since there are four students with the subject 'Maths', the student with higher marks is assigned the rank 1 and 2, 3 and 4, respectively.

What is DENSE_RANK Function in SQL?

The DENSE RANK function and the RANK function share similarities. But slightly different from that of the rank function. It produces a Rank continuously without any gap.

Some essential points need to be kept in mind while using the DENSE_RANK function:

Rows with identical values receive the same Rank.

The Rank of subsequent rows increases by one.

Syntax:

SELECT col_name
DENSE_RANK() OVER(
[PARTITION BY exp] // if you want to partition in group
ORDER BY exp [ASC | DESC], [{exp1…}]
AS ‘r’ FROM table_name

Examples of DENSE_RANK Function

Let’s see an example, to understand the Dense_Rank function better. Let’s consider Score table which has attributes like name, subject and marks.

Id

name

subject

marks

1

Ninja_1

Maths

85

2

Ninja_2

Maths

50

3

Ninja_3

Science

70

4

Ninja_4

Economics

85

5

Ninja_5

Maths

20

6

Ninja_6

English

92

7

Ninja_7

English

92

8

Ninja_8

Science

89

9

Ninja_9

Maths

63

The above table (score) contains six rows and four columns; for better understanding, let’s see an example using Dense_Rank without the PARTITION clause.

Estimating DENSE_RANK without using Partition Function

SELECT
name, subject, marks,
DENSE_RANK() OVER (
ORDER BY marks ASC
) AS rank
FROM Result;

Output:

name

subject

marks

rank

Ninja_5

Maths

20

1

Ninja_2

Maths

50

2

Ninja_9

Maths

63

3

Ninja_3

Science

70

4

Ninja_1

Maths

85

5

Ninja_4

Economics

85

5

Ninja_8

Science

89

6

Ninja_6

English

92

7

Ninja_7

English

92

7

Since Ninja_1 and Ninja_4 have the same marks, they are given the same Rank. And Ninja_8 is given Rank 6.

Another example is DENSE_RANK with PARTITION clause.

Estimating DENSE_RANK using Partition Function

SELECT
name, subject, marks,
DENSE_RANK() OVER (
PARTITION BY subject
ORDER BY marks ASC
) AS rank
FROM Result;

The next Rank is skipped if two or more rank rows have identical values in the ORDER BY columns.

The next Rank is not skipped if two or more rows have the same values in the ORDER BY columns and obtain the same dense Rank.

They do not follow a chronological order because the skipped ranks.

They follow a chronological order.

Example: If two workers have the same value, for instance, they will both obtain rank 1, and the following employee will receive rank 3.

Example: If two workers have the same value, for instance, they will both be assigned rank 1, and the subsequent employee will be given a rank of 2.

A quick summary of SQL ranking function

RANK : The rank function is a SQL function specifically used to calculate each row's ranking depending on the defined attributes and clauses. It skips a rank who has the same record values.

DENSE_RANK : The dense_rank function is a SQL function which assigns rank number to each row. It does not skip any rank who has the same record values.

What is the difference between rank () and Dense_rank ()?

RANK() assigns the same rank to rows with equal values, leaving gaps. DENSE_RANK() assigns the same rank to equal values without gaps, resulting in consecutive ranks.

What is difference between rank () ROW_NUMBER () and Dense_rank () in SQL?

RANK() assigns the same rank with gaps for equal values.

DENSE_RANK() assigns the same rank without gaps for equal values.

ROW_NUMBER() assigns a unique rank to each row.

What is Dense_rank ()?

DENSE_RANK() is a SQL window function that assigns a unique rank to each row in the result set, giving the same rank to rows with equal values without gaps.

What is the difference between rank and Dense_rank in postgresql?

In PostgreSQL, RANK() assigns the same rank to rows with equal values and leaves gaps before the next rank, while DENSE_RANK() assigns the same rank to equal values without gaps, creating consecutive rank values for ties.

Conclusion

In conclusion, the RANK DENSE_RANK functions in SQL are powerful queries to handle ranking and identifying patterns in data. We discuss how this function works and how to use it effectively. We can also use partition functions which make them more unique.

Different people, like analysts and data professionals, are using this technique to make more informed decisions. However, there is much more to learn about this domain, including advanced SQL techniques. Do check out the article below for more information on SQL.

You can find more informative articles or blogs on our platform. You can also practice more coding problems and prepare for interview questions from well-known companies on your platform, Coding Ninjas Studio.