RSS

Tag Archives: SELECT / GROUP BY – sequence of a value

group by sequence number MySQL


Before you use below sample code i prefer you use this URL code which better for you.

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

 

SAMPLE DataTable

CREATE TABLE CountryCity
 (
 city VARCHAR(100),
 country VARCHAR(100),
 population int
 );

INSERT INTO CountryCity VALUES ('Lahore', 'Pakistan',8175133); 
 INSERT INTO CountryCity VALUES ('Multan', 'Pakistan',3792621); 
 INSERT INTO CountryCity VALUES ('Liquat Pur', 'Pakistan',2695598); 
 
 INSERT INTO CountryCity VALUES ('Kalkata', 'India',2181000);
 INSERT INTO CountryCity VALUES ('Dehli', 'India',808000);
 INSERT INTO CountryCity VALUES ('Goya', 'India',422000);
 
 INSERT INTO CountryCity VALUES ('Riyadh', 'KSA',7825300);
 INSERT INTO CountryCity VALUES ('Madina', 'KSA',1016800);
 INSERT INTO CountryCity VALUES ('Makkah', 'KSA',770800);

MySQL Query to Get Top 2

To get the 2 largest Country cities for each country, you can use the following query in MySQL:
SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM CountryCity
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 2;

 

The query returns:

 


city
country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621

 

How It Works

Explanation of the MySQL query:

  • Session Variables

Currently MySQL does not support ROW_NUMBER() function that can assign a sequence number within a group, but as a workaround we can use MySQL session variables.

These variables do not require declaration, and can be used in a query to do calculations and to store intermediate results.

@current_country := country

This code is executed for each row and stores the value of country column to @current_country variable.

@country_rank := IF(@current_country = country, @country_rank + 1, 1)

In this code, if @current_country is the same we increment rank, otherwise set it to 1. For the first row@current_country is NULL, so rank is also set to 1.

For correct ranking, we need to have ORDER BY country, population DESC

So if we just execute the subquery:

   SELECT city, country, population, 
       @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
       @current_country := country 
  FROM Countrycities
  ORDER BY country, population DESC

We get the list of cities ranked by population within the country:

city country population country_rank current_country
Kalkata India 2181000 1 India
Dehli India 808000 2 India
Goya India 422000 3 India
riyadh KSA 7825300 1 KSA
Madina KSA 1016800 2 KSA
Makkah KSA 770800 3 KSA
Lahore Pakistan 8175133 1 Pakistan
Multan Pakistan 3792621 2 Pakistan
Liquat Pur Pakistan 2695598 3 Pakistan
  • Selecting Range

When we have a rank assigned to each city within its country, we can retrieve the required range:

   -- Get top 2 for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank <= 2;
 
   -- Get the city with 3rd population for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank = 3;

ROW_NUMBER() – Oracle, SQL Server and PostgreSQL

In Oracle, SQL Server and PostgreSQL you can achieve the same functionality using ROW_NUMBER function:

   SELECT city, country, population
   FROM
    (SELECT city, country, population, 
                  ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
      FROM cities) ranked
   WHERE country_rank <= 2;

This query works in Oracle, SQL Server and PostgreSQL without any changes and returns:

city country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621
Advertisements
 
Leave a comment

Posted by on February 12, 2015 in My SQL

 

Tags: , , ,