OpenLampTech

Share this post

MySQL ROW_NUMBER() Window Function with CASE Expression

openlamptech.substack.com

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.

Joshua Otwell
Feb 8, 2022
Share this post

MySQL ROW_NUMBER() Window Function with CASE Expression

openlamptech.substack.com

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.


Thanks for reading OpenLampTech! Subscribe to the free weekly newsletter for all the great original and curated PHP/MySQL content I share in the publication.


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);

Sakila city table rows with country_id columns

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.

MySQL query results for rows base on ROW_NUMBER()
Query results with ROW_NUMBER() window function value.

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;

MySQL CASE expression
CASE expression and NULL values.

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.

Tell me more!


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;

MySQL results set for single row groups
The final result set of multiple rows to single row per group

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;

MIN() aggregate function returns the same query results.

Similar content
OpenLampTech
MySQL SHOW TABLES - 2 variations
This post is an excerpt from one of my premium MySQL Beginner blog posts and content. If you are new to MySQL and want to learn how to use the database and get the data you need from it, I am making these premium posts and content for you…
Read more
a year ago · Joshua Otwell
OpenLampTech
OpenLampTech issue #12 - My RTC Interview
Hey glad to have you here! 👍 If someone shared this newsletter with you and you are not yet subscribed, please use the Subscribe button below and join:Thanks for reading OpenLampTech! Subscribe (it’s free) and receive new posts by email, be part of a great community, and support my work…
Read more
a year ago · Joshua Otwell

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!!!

Thank you for reading OpenLampTech. This post is public so please share it. Thank you!

Share


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.💰

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

Share this post

MySQL ROW_NUMBER() Window Function with CASE Expression

openlamptech.substack.com
Comments
TopNewCommunity

No posts

Ready for more?

© 2023 Joshua Otwell
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing