In the last article we have seen on how to get first, second or third highest from the data. But suppose there are multiple categories present in the column and for each category we want the record which has the highest/lowest value for another attribute.
In this case, ROW_NUMBER() will be used along with the “Partition By” clause.
Suppose data is present in this format:
Here for each Country, we want the state which has the highest population.
Expected Output:
Query to assign Rank:
SELECT
CountryName,
StateName,
Population,
ROW_NUMBER() OVER(PARTITION BY CountryName ORDER BY Population Desc) as Rank
FROM Country_Wise_Data;
For each country, it will assign the rank. The Highest Populated state will be assigned with Rank 1. If you want Lowest Population state to be assigned as Rank 1, you need to use “Order by Population asc”
Once we have the ranks assigned, we can simply write one more query to filter out and consider the records where rank is 1 only.
Query:
SELECT
CountryName,
StateName,
Population
FROM
(
SELECT
CountryName,
StateName,
Population,
ROW_NUMBER() OVER(PARTITION BY CountryName ORDER BY Population Desc) as Rank
FROM Country_Wise_Data
) as Temp
Where Temp.Rank = 1;
and we are done.
We collect cookies and may share with 3rd party vendors for analytics, advertising and to enhance your experience. You can read more about our cookie policy by clicking on the 'Learn More' Button. By Clicking 'Accept', you agree to use our cookie technology.
Our Privacy policy can be found by clicking here