Practice Now
GROUP_CONCAT
ORDER BY
SQL
database
MySQL

GROUP_CONCAT ORDER BY

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

Introduction

GROUP_CONCAT in MySQL concatenates values from multiple rows into a single string, grouped by a specified column. Adding ORDER BY inside GROUP_CONCAT controls the order of the concatenated values. This is different from an ORDER BY on the outer query — the outer ORDER BY sorts the result rows, while ORDER BY inside GROUP_CONCAT sorts the values within each concatenated string. This function is MySQL-specific; PostgreSQL uses STRING_AGG() and SQL Server uses STRING_AGG() or FOR XML PATH.

Basic Syntax

sql
1SELECT
2    group_column,
3    GROUP_CONCAT(value_column ORDER BY sort_column ASC SEPARATOR ', ')
4FROM table_name
5GROUP BY group_column;

Example: Students and Their Courses

sql
1-- Sample data
2CREATE TABLE enrollments (
3    student_name VARCHAR(50),
4    course VARCHAR(50),
5    grade CHAR(1)
6);
7
8INSERT INTO enrollments VALUES
9('Alice', 'Math', 'A'),
10('Alice', 'Physics', 'B'),
11('Alice', 'Chemistry', 'A'),
12('Bob', 'Math', 'C'),
13('Bob', 'History', 'A'),
14('Bob', 'Physics', 'B'),
15('Charlie', 'Chemistry', 'B'),
16('Charlie', 'Math', 'A');
17
18-- Courses per student, ordered alphabetically
19SELECT
20    student_name,
21    GROUP_CONCAT(course ORDER BY course ASC SEPARATOR ', ') AS courses
22FROM enrollments
23GROUP BY student_name;

Result:

student_namecourses
AliceChemistry, Math, Physics
BobHistory, Math, Physics
CharlieChemistry, Math

Without ORDER BY, the concatenation order is undefined.

ORDER BY with Different Columns

sql
1-- Order courses by grade (best first)
2SELECT
3    student_name,
4    GROUP_CONCAT(course ORDER BY grade ASC SEPARATOR ', ') AS courses_by_grade
5FROM enrollments
6GROUP BY student_name;
student_namecourses_by_grade
AliceChemistry, Math, Physics
BobHistory, Physics, Math
CharlieMath, Chemistry

Alice's A-grade courses (Chemistry, Math) come before B-grade (Physics).

Multiple ORDER BY Columns

sql
1-- Order by grade, then alphabetically within same grade
2SELECT
3    student_name,
4    GROUP_CONCAT(
5        course
6        ORDER BY grade ASC, course ASC
7        SEPARATOR ', '
8    ) AS courses
9FROM enrollments
10GROUP BY student_name;

DISTINCT with ORDER BY

sql
1-- Remove duplicates before concatenating
2SELECT
3    department,
4    GROUP_CONCAT(DISTINCT skill ORDER BY skill SEPARATOR ', ') AS skills
5FROM employees
6GROUP BY department;

DISTINCT removes duplicate values before concatenation. The ORDER BY applies after deduplication.

Custom Separator

sql
1-- Semicolon separator
2SELECT
3    student_name,
4    GROUP_CONCAT(course ORDER BY course SEPARATOR '; ') AS courses
5FROM enrollments
6GROUP BY student_name;
7-- Alice | Chemistry; Math; Physics
8
9-- Newline separator (useful for exports)
10SELECT
11    student_name,
12    GROUP_CONCAT(course ORDER BY course SEPARATOR '\n') AS courses
13FROM enrollments
14GROUP BY student_name;
15
16-- No separator
17SELECT
18    student_name,
19    GROUP_CONCAT(course ORDER BY course SEPARATOR '') AS courses
20FROM enrollments
21GROUP BY student_name;
22-- Alice | ChemistryMathPhysics

Concatenating Multiple Columns

sql
1-- Combine course and grade into each concatenated value
2SELECT
3    student_name,
4    GROUP_CONCAT(
5        CONCAT(course, ' (', grade, ')')
6        ORDER BY course ASC
7        SEPARATOR ', '
8    ) AS course_grades
9FROM enrollments
10GROUP BY student_name;
student_namecourse_grades
AliceChemistry (A), Math (A), Physics (B)
BobHistory (A), Math (C), Physics (B)

Increasing the Length Limit

GROUP_CONCAT has a default maximum length of 1024 characters. Longer results are silently truncated.

sql
1-- Check current limit
2SHOW VARIABLES LIKE 'group_concat_max_len';
3-- 1024
4
5-- Increase for current session
6SET SESSION group_concat_max_len = 1000000;
7
8-- Increase globally (requires privileges)
9SET GLOBAL group_concat_max_len = 1000000;

PostgreSQL Equivalent: STRING_AGG

sql
1-- PostgreSQL — STRING_AGG with ORDER BY
2SELECT
3    student_name,
4    STRING_AGG(course, ', ' ORDER BY course ASC) AS courses
5FROM enrollments
6GROUP BY student_name;

Note: In STRING_AGG, the separator comes before ORDER BY, unlike MySQL's GROUP_CONCAT where ORDER BY comes before SEPARATOR.

SQL Server Equivalent

sql
1-- SQL Server 2017+ — STRING_AGG
2SELECT
3    student_name,
4    STRING_AGG(course, ', ') WITHIN GROUP (ORDER BY course ASC) AS courses
5FROM enrollments
6GROUP BY student_name;

Common Pitfalls

  • Silent truncation at 1024 characters: The default group_concat_max_len is 1024. Results longer than this are silently truncated with no warning. Always increase this setting when concatenating many values or long strings.
  • Confusing inner ORDER BY with outer ORDER BY: GROUP_CONCAT(col ORDER BY col) orders values within the concatenated string. ORDER BY at the end of the query orders the result rows. They are independent and serve different purposes.
  • NULL values: GROUP_CONCAT ignores NULL values. If a column contains NULLs, they are silently excluded from the concatenated result. Use COALESCE(column, 'N/A') if you want to include a placeholder for NULLs.
  • Not using DISTINCT when needed: If joins produce duplicate rows, GROUP_CONCAT includes duplicates in the output. Use GROUP_CONCAT(DISTINCT ...) to eliminate them.
  • Assuming GROUP_CONCAT exists in all databases: GROUP_CONCAT is MySQL/MariaDB-specific. PostgreSQL uses STRING_AGG(), SQL Server 2017+ uses STRING_AGG() with WITHIN GROUP, and SQLite uses GROUP_CONCAT() (with different syntax for ordering).

Summary

  • GROUP_CONCAT(col ORDER BY sort_col SEPARATOR ', ') concatenates and sorts values within each group
  • The inner ORDER BY sorts the concatenated values; the outer ORDER BY sorts result rows
  • Use DISTINCT to remove duplicates before concatenation
  • Default max length is 1024 characters — increase group_concat_max_len for longer results
  • PostgreSQL equivalent: STRING_AGG(col, ', ' ORDER BY col)
  • SQL Server equivalent: STRING_AGG(col, ', ') WITHIN GROUP (ORDER BY col)
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.