Using WITHIN GROUP to Order STRING_AGG Results

When was the last time you wrote a SQL query and knew something was possible but just couldn’t remember how? I had one of those moments this week with STRING_AGG and ordering data, and although it was frustrating, I knew it would make a worthwhile blog post. Let’s look at some examples using STRING_AGG and WITHIN GROUP (aka the clause that slipped my mind).

What is STRING_AGG?

STRING_AGG, introduced in SQL Server 2017, allows you to easily concatenate strings however you choose. Do you want to delimit by commas? Easy. Delimit by pipes? No problem? Delimit by the word “and” so that reads out “x and y and z and…” Go for it. Who am I to judge?

What may seem tricky at first with STRING_AGG is ordering your results. It’s not as simple as using ORDER BY. Let’s look at an example of using WITHIN GROUP, which can be used to order results when using STRING_AGG. We’ll test using the Badges table in the StackOverflow2013 database.

Let’s say we want to gather some information on Badge counts. We’ll grab any name with a count over 600,000. We can run something like this:

SELECT Name, COUNT(*) AS 'Badge_Count'
FROM Badges
GROUP BY Name
HAVING COUNT(*) > 600000
ORDER BY COUNT(*) DESC;
GO

Now, what if we want to sum the Badge_Count values and put those three names in one field, separated by a comma? One option would be to use a CTE and run this:

WITH CTE
AS (
	SELECT Name, COUNT(*) AS 'Badge_Count'
	FROM Badges
	GROUP BY Name
	HAVING COUNT(*) > 600000
	)
SELECT STRING_AGG(Name, ',') AS 'Name',
	SUM(Badge_Count) AS 'Badge_Sum'
FROM CTE;
GO

We Want Order

That’s great. Where things could get tricky is if the request calls for the list of names to be in a specific order. Our first query showed Popular Question as the highest count, followed by Student, and then Editor. What if we need to have our comma-separated list in that order? Here is where we use WITHIN GROUP along with STRING_AGG.

WITH CTE
AS (
	SELECT Name, COUNT(*) AS 'Badge_Count'
	FROM Badges
	GROUP BY Name
	HAVING COUNT(*) > 600000
	)
SELECT STRING_AGG(Name, ',') 
      WITHIN GROUP (ORDER BY Badge_Count DESC) as 'Name', 
	SUM(Badge_Count) AS 'Badge_Sum'
FROM CTE;
GO

Now we have the same order of our first query.

WITHIN GROUP was Within Reach

We’ve shown how WITHIN GROUP can be utilized to order data when using the STRING_AGG function. WITHIN GROUP is easy to use; you just have to remember what it’s called.

Thanks for reading!