In the
A challenge for 2016....... accepted! post I said that I would write a post once every month that would explain very basic stuff about SQL Server. Today is the second post, in this post we will take a look at how you can return the row with the maximum value for a group.
In order to explain the code in this post, I decided to grab the top largest cities in the world as the data. You can find the data here
List of cities proper by population
Below is the SQL code to create a table and also the data to populate this table
create table #Temp( Rank int not null,
City varchar(100) not null,
Population int not null,
Density int not null,
Country varchar(100))
INSERT #Temp
SELECT 1,'Shanghai',24256800,3826,'China' union all
SELECT 2,'Karachi',23500000,6663,'Pakistan' union all
SELECT 3,'Beijing',21516000,1311,'China' union all
SELECT 4,'Lagos',21324000,18206,'Nigeria' union all
SELECT 5,'Delhi',16787941,11320,'India' union all
SELECT 6,'Tianjin',15200000,1293,'China' union all
SELECT 7,'Istanbul',14160467,2593,'Turkey' union all
SELECT 8,'Tokyo',13297629,6075,'Japan' union all
SELECT 9,'Guangzhou',13080500,1759,'China' union all
SELECT 10,'Mumbai',12478447,20680,'India' union all
SELECT 11,'Moscow',12197596,4859,'Russia' union all
SELECT 12,'São Paulo',11895893,7821,'Brazil' union all
SELECT 13,'Shenzhen',10467400,5256,'China' union all
SELECT 14,'Jakarta',10075310,15171,'Indonesia' union all
SELECT 15,'Seoul',10048850,17134,'South Korea'
Now that we have the data in our table, let's run a simple query to return the row with the most populous city in the world. You can accomplish this in SQL Server by using the TOP operator and ordering the data by population in descending order.
SELECT TOP 1 * FROM #Temp
ORDER BY population DESC
Here is the output from that query
Rank City Population Density Country
1 Shanghai 24256800 3826 China
What we want to do next is return one row the most populous city per country.
So let's take it a step at a time, the query below will give you the maximum population by country
SELECT MAX(population) as BiggestPopulation,Country
FROM #Temp
GROUP BY Country
ORDER BY BiggestPopulation DESC
Here is the output
BiggestPopulation Country
24256800 China
23500000 Pakistan
21324000 Nigeria
16787941 India
14160467 Turkey
13297629 Japan
12197596 Russia
11895893 Brazil
10075310 Indonesia
10048850 South Korea
What we can do is join the query we have above with the table itself on the country and population columns. The query will look like this
SELECT T.* FROM(
SELECT MAX(population) as BiggestPopulation,Country
FROM #Temp
GROUP BY Country) X
JOIN #Temp t on x.BiggestPopulation = t.Population
AND t.Country = x.Country
ORDER BY t.Population DESC
Here is the output
+------+-----------+------------+---------+-------------+
| Rank | City | Population | Density | Country |
+------+-----------+------------+---------+-------------+
| 1 | Shanghai | 24256800 | 3826 | China |
+------+-----------+------------+---------+-------------+
| 2 | Karachi | 23500000 | 6663 | Pakistan |
+------+-----------+------------+---------+-------------+
| 4 | Lagos | 21324000 | 18206 | Nigeria |
+------+-----------+------------+---------+-------------+
| 5 | Delhi | 16787941 | 11320 | India |
+------+-----------+------------+---------+-------------+
| 7 | Istanbul | 14160467 | 2593 | Turkey |
+------+-----------+------------+---------+-------------+
| 8 | Tokyo | 13297629 | 6075 | Japan |
+------+-----------+------------+---------+-------------+
| 11 | Moscow | 12197596 | 4859 | Russia |
+------+-----------+------------+---------+-------------+
| 12 | São Paulo | 11895893 | 7821 | Brazil |
+------+-----------+------------+---------+-------------+
| 14 | Jakarta | 10075310 | 15171 | Indonesia |
+------+-----------+------------+---------+-------------+
| 15 | Seoul | 10048850 | 17134 | South Korea |
+------+-----------+------------+---------+-------------+
SQL Server 2005 introduced a bunch of ranking/windowing functions which make writing these queries a little simpler. We are going to use ROW_NUMBER for this post. Here is what the query will look like
SELECT ROW_NUMBER() OVER (PARTITION By [what do you want it grouped/partitioned by?] ORDER BY [what do you want it ordered by?]) AS Row,*
FROM Table
In our case, we want to partition the result set by country, we also want to order each partition by population descending. The query looks like this
SELECT ROW_NUMBER() OVER (PARTITION By Country ORDER BY population DESC) AS Row,*
FROM #Temp
ORDER BY Row,Population DESC
Here is what that query returns
+-----+------+-----------+------------+---------+-------------+
| Row | Rank | City | Population | Density | Country |
+-----+------+-----------+------------+---------+-------------+
| 1 | 1 | Shanghai | 24256800 | 3826 | China |
+-----+------+-----------+------------+---------+-------------+
| 1 | 2 | Karachi | 23500000 | 6663 | Pakistan |
+-----+------+-----------+------------+---------+-------------+
| 1 | 4 | Lagos | 21324000 | 18206 | Nigeria |
+-----+------+-----------+------------+---------+-------------+
| 1 | 5 | Delhi | 16787941 | 11320 | India |
+-----+------+-----------+------------+---------+-------------+
| 1 | 7 | Istanbul | 14160467 | 2593 | Turkey |
+-----+------+-----------+------------+---------+-------------+
| 1 | 8 | Tokyo | 13297629 | 6075 | Japan |
+-----+------+-----------+------------+---------+-------------+
| 1 | 11 | Moscow | 12197596 | 4859 | Russia |
+-----+------+-----------+------------+---------+-------------+
| 1 | 12 | São Paulo | 11895893 | 7821 | Brazil |
+-----+------+-----------+------------+---------+-------------+
| 1 | 14 | Jakarta | 10075310 | 15171 | Indonesia |
+-----+------+-----------+------------+---------+-------------+
| 1 | 15 | Seoul | 10048850 | 17134 | South Korea |
+-----+------+-----------+------------+---------+-------------+
| 2 | 3 | Beijing | 21516000 | 1311 | China |
+-----+------+-----------+------------+---------+-------------+
| 2 | 10 | Mumbai | 12478447 | 20680 | India |
+-----+------+-----------+------------+---------+-------------+
| 3 | 6 | Tianjin | 15200000 | 1293 | China |
+-----+------+-----------+------------+---------+-------------+
| 4 | 9 | Guangzhou | 13080500 | 1759 | China |
+-----+------+-----------+------------+---------+-------------+
| 5 | 13 | Shenzhen | 10467400 | 5256 | China |
+-----+------+-----------+------------+---------+-------------+
What we really want is the top 10 rows. You might think that you can just add WHERE Row = 1 to the query. This is not allowed since the WHERE clause gets executed before the ROW_Number function as part of the SELECT. What you can do is use a common table expression and then specify WHERE Row = 1 when selecting from the CTE
Here is what it looks like
;WITH CTE AS(
SELECT ROW_NUMBER() OVER (PARTITION By Country ORDER BY population DESC) AS Row,*
FROM #Temp)
SELECT Rank, City,Population,Density,Country
FROM CTE
WHERE Row = 1
ORDER BY Population DESC
And here is our output, for each country we have only one row, you can also see that 4 cities from the top 10 are not in this list, those cities are in either China or India
+------+-----------+------------+---------+-------------+--+
| Rank | City | Population | Density | Country | |
+------+-----------+------------+---------+-------------+--+
| 1 | Shanghai | 24256800 | 3826 | China | |
+------+-----------+------------+---------+-------------+--+
| 2 | Karachi | 23500000 | 6663 | Pakistan | |
+------+-----------+------------+---------+-------------+--+
| 4 | Lagos | 21324000 | 18206 | Nigeria | |
+------+-----------+------------+---------+-------------+--+
| 5 | Delhi | 16787941 | 11320 | India | |
+------+-----------+------------+---------+-------------+--+
| 7 | Istanbul | 14160467 | 2593 | Turkey | |
+------+-----------+------------+---------+-------------+--+
| 8 | Tokyo | 13297629 | 6075 | Japan | |
+------+-----------+------------+---------+-------------+--+
| 11 | Moscow | 12197596 | 4859 | Russia | |
+------+-----------+------------+---------+-------------+--+
| 12 | São Paulo | 11895893 | 7821 | Brazil | |
+------+-----------+------------+---------+-------------+--+
| 14 | Jakarta | 10075310 | 15171 | Indonesia | |
+------+-----------+------------+---------+-------------+--+
| 15 | Seoul | 10048850 | 17134 | South Korea | |
+------+-----------+------------+---------+-------------+--+
That is all for this month, see you next month. In the meantime, make sure to explore windowing/ranking functions as well as Common Table Expressions
