

Discover more from OpenLampTech
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