MySQL ROW_NUMBER() Window Function with CASE Expression
MySQL ROW_NUMBER() Window Function with CASE Expression
Use MySQL ROW_NUMBER() window function for multiple rows grouping to a single row using a CASE expression.
In this post, I am covering how to use the MySQL ROW_NUMBER() window function and an CASE expression to collapse multiple rows of data into a single row per group.
Data set up
In order for a more manageable set of data to work with, I am filtering the sakila database ‘city’ table for any rows whose country_id value is in the list of (26,27,28,29) using the IN() operator:
SELECT country_id, city
FROM city
WHERE country_id IN (26,27,28,29);
ROW_NUMBER() window function
In this Common Table Expression (CTE), the ROW_NUMBER() window function assigns an incrementing INTEGER value to each row in the result set. Since the PARTITION BY clause is present, sub-groups are formed based on the unique country_id column values and we are assigning order based on the ORDER BY city ASC clause:
WITH data_set AS (
SELECT country_id,
city,
ROW_NUMBER() OVER(PARTITION BY country_id ORDER BY city ASC) AS rn
FROM city
WHERE country_id IN (26,27,28,29)
)
SELECT * FROM data_set;Notice ROW_NUMBER() resets the incrementing INTEGER value for each distinct country_id sub-group.
Support OpenLampTech, my blog, and my work with a coffee if you would like.☕
CASE expression and NULLs
Next, we use a CASE expression and retrieve each city column value based on what the rn alias value is. In those instances where the WHEN predicate is not true, NULL is returned for that city column value:
SELECT country_id,
CASE WHEN rn = 1 THEN city ELSE NULL END AS city_1,
CASE WHEN rn = 2 THEN city ELSE NULL END AS city_2,
CASE WHEN rn = 3 THEN city ELSE NULL END AS city_3,
CASE WHEN rn = 4 THEN city ELSE NULL END AS city_4,
CASE WHEN rn = 5 THEN city ELSE NULL END AS city_5,
CASE WHEN rn = 6 THEN city ELSE NULL END AS city_6
FROM data_set;
We are almost there with the query results we have so far. However, we need to further collapse the rows now into a single row per country_id value.
Do you need to learn MySQL? Just starting out and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don't know where to start. Learn more about my premium MySQL blog posts and content as I develop and release them.
Use Aggregate functions to collapse NULL rows
In order to collapse the multiple rows to a single row per group, we simply need to wrap each CASE expression in either the MAX() or MIN() aggregate function and GROUP BY the country_id value:
SELECT country_id,
MAX(CASE WHEN rn = 1 THEN city ELSE NULL END) AS city_1,
MAX(CASE WHEN rn = 2 THEN city ELSE NULL END) AS city_2,
MAX(CASE WHEN rn = 3 THEN city ELSE NULL END) AS city_3,
MAX(CASE WHEN rn = 4 THEN city ELSE NULL END) AS city_4,
MAX(CASE WHEN rn = 5 THEN city ELSE NULL END) AS city_5,
MAX(CASE WHEN rn = 6 THEN city ELSE NULL END) AS city_6
FROM data_set
GROUP BY country_id;
As shown in this similar query, replacing the MAX() aggregate function with MIN() yields the same results:
SELECT country_id,
MIN(CASE WHEN rn = 1 THEN city ELSE NULL END) AS city_1,
MIN(CASE WHEN rn = 2 THEN city ELSE NULL END) AS city_2,
MIN(CASE WHEN rn = 3 THEN city ELSE NULL END) AS city_3,
MIN(CASE WHEN rn = 4 THEN city ELSE NULL END) AS city_4,
MIN(CASE WHEN rn = 5 THEN city ELSE NULL END) AS city_5,
MIN(CASE WHEN rn = 6 THEN city ELSE NULL END) AS city_6
FROM data_set
GROUP BY country_id;
Similar content
Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.
If you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate content and are much appreciated.
Thank you for reading!!!
How can I help you?
Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. Let’s both save money on the plans offered. 💸
Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. ✉️
Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.
I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.
Grab a free pack of mobile Creator wallpapers with my referral link.
Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.
Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰
Disclosure: Some of the services and products links in this email are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
Cover photo credits: Image by OpenClipart-Vectors from Pixabay