This article will introduce important functions in SQL rank, denserank, over, partition.
This article will introduce important functions in SQL rank, denserank, over, partition.
There are some important SQL functions we can study, like
RANK() and
DENSE_RANK()
are both effort-saving and effective functions to learn in SQL. The RANK()
the function simply works like giving rank 1 to the student who earns the maximum marks in the exam, giving rank 2 to the student who scores the second maximum marks in the exam, and so on.
Let’s understand the details with the following examples: Suppose the current position of football-playing countries in the FIFA ranking system is as given in the table.
Team | Total Points | Previous Points | Delta (+/-) |
---|---|---|---|
Brazil | 1827 | 1823.42 | 9.27 |
Belgium | 1827 | 1828.45 | -1.45 |
France | 1789 | 1786.15 | 3.7 |
Argentina | 1765 | 1766.99 | -1.86 |
England | 1761 | 1755.52 | 6.19 |
The second column in this table displays the total points earned by each country in the FIFA ranking system. Now we want to apply functions RANK()
and DENSE_RANK()
on the basis of total points.
We can see that Brazil and Belgium have the same number of total points.
SELECT *,
RANK () OVER( ORDER BY TOTAL POINTS asc) as RANK
FROM ‘FIFA_table’
ORDER BY RANK;
Rank | ||||
Team | Total Points | Previous Points | Delta(+/-) | Rank |
Brazil | 1827 | 1823.42 | 9.25 | 1 |
Belgium | 1827 | 1828.45 | -1.45 | 1 |
France | 1789 | 1786.15 | 3.7 | 3 |
Argentina | 1765 | 1786.99 | -1.86 | 4 |
England | 1761 | 1755.52 | 6.19 | 5 |
Countries that have equal total points are given the same rank, and for countries that have fewer adjacent points, the rank will be given after considering the positions of previous rank holders.
We can also apply RANK() category- or division-wise. This can be easily understood from the following example, where we will be giving a ranking to the dataset according to each division or region in which football-playing countries are categorized, viz; UEFA, CONMEBOL.
Suppose we have this table.
Team | Total Points | Previous Points | Delta(+/-) | Division |
---|---|---|---|---|
Brazil | 1827 | 1823.42 | 9.27 | CONMEBOL |
Belgium | 1827 | 1828.45 | -1.45 | UEFA |
France | 1789 | 1786.15 | 3.7 | UEFA |
Argentina | 1765 | 1766.99 | -1.86 | CONMEBOL |
England | 1761 | 1755.52 | 6.19 | UEFA |
SELECT *,
RANK () OVER( PARTITION by Division ORDER by Total Points asc) as RANK
FROM ‘FIFA_table’
ORDER by RANK;
After applying the code for RANK() with PARTITION() of any category.
Team | Total Points | Previous Points | Delta(+/-) | Division | Rank |
---|---|---|---|---|---|
Brazil | 1827 | 1823.42 | 9.27 | CONMEBOL | 1 |
Argentina | 1765 | 1766.99 | -1.86 | CONMEBOL | 2 |
Belgium | 1827 | 1828.45 | -1.45 | UEFA | 1 |
France | 1789 | 1786.15 | 3.7 | UEFA | 2 |
England | 1761 | 1755.52 | 6.19 | UEFA | 3 |
DENSE_RANK() will rank data, but it does not take into account the position of other rank holders. This can be seen in the following example.
SELECT *,
DENSE_RANK () OVER( ORDER BY TOTAL POINTS asc) as RANK
FROM ‘FIFA_table’
ORDER BY RANK;
Team | Total Points | Previous Points | Delta(+/-) | Division | Rank |
---|---|---|---|---|---|
Brazil | 1827 | 1823.42 | 9.27 | CONMEBOL | 1 |
Belgium | 1827 | 1826.45 | -1.45 | UEFA | 1 |
France | 1789 | 1786.15 | 3.7 | UEFA | 2 |
Argentina | 1765 | 1766.99 | -1.86 | CONMEBOL | 3 |
England | 1761 | 1755.52 | 6.19 | UEFA | 4 |
Countries that have equal total points are given the same rank, the same rank, for rest of countries ranks will be given according to total points. The adjacent ranks will be given to countries without considering the row positions of previous rank holders.
In simple words, Brazil and Belgium have equal total points, which is the highest number of total points in the table, so they are given rank 1. France has the second-highest total of points in the table, so it is given rank 2, despite being in row position 3.
This is contrary to RANK() and that’s why it explains the difference between RANK() and DENSE_RANK().
ANCOVA is an extension of ANOVA (Analysis of Variance) that combines blocks of regression analysis and ANOVA. Which makes it Analysis of Covariance.
What if we learn topics in a desirable way!! What if we learn to write Python codes from gamers data !!
Start using NotebookLM today and embark on a smarter, more efficient learning journey!
This can be a super guide for you to start and excel in your data science career.
In this following article we will familiarize with dictionary and it’s numerous functionalities that makes it so versatile.
Learn to store bytes and byte-arrays. Learn to convert normal datatypes to byte sequence. The bytes() function in Python returns an immutable bytes sequence.
At its heart, the `print()` function sends data to the standard output, typically the console.
List is one of the four data types in Python. Python allows us to create a heterogeneous collection of items inside a list.
NLP is a branch of AI that concerns with computers(AI) understanding natural languages.
Booleans are most important aspects of programming languages.
Booleans are most important aspects of programming languages.
Sentiment analysis can determine the polarity of sentiments from given sentences. We can classify them into certain ranges positive, neutral, negative
Strings is one of the important fundamental datatypes in python. Interactions of input and output console’s are conveyed using strings.
Read this article further to know where to use stemmers and lemmatization. Lemmatization maybe better than stemmer but is it worth your time.