A Slice of Time: Ordered Set Function(s)

This is part six in a series on window functions in SQL Server.

One of a Kind: The Ordered Set Function

As of SQL Server 2019, there is only one ordered set function: STRING_AGG(). I like STRING_AGG() a lot, especially because it means my days of needing to explain the STUFF() + FOR XML PATH trick to concatenate values together in SQL Server are numbered.

STRING_AGG() is interesting in that we categorize it as a window function and yet it violates my first rule of window functions: there isn’t an OVER() clause. Instead, it accepts but does not require a WITHIN GROUP() clause. Let’s see it in action.

Sales Territories

The Wide World Importers database has a table, Application.StateProvinces, which includes the state/province code and the sales territory controlling that state or province. It looks a bit like so:

States and provinces. But mostly states.

Suppose we want to see which states (and provinces) are in each sales territory, and we want a comma-delimited list of these states (…and provinces). That’s easy: I’ll just use my STUFF() FOR XML PATH trick.

Frankly, Robin kind of deserved that one.

There’s a better way.

The Better Way

Let’s get sales territory and a list of states separated by commas.

SELECT
    SalesTerritory,
    STRING_AGG(StateProvinceCode, ',')  AS StatesList
FROM Application.StateProvinces s
GROUP BY
    SalesTerritory;

The provinces were a lie.

This is great, but I’m a stickler for lexicographic ordering and want my states ordered accordingly. Please make that happen or I will open a sev 1 trouble ticket and will call about it every 20 minutes until you complete the task.

Bringing Order to the Universe

To create an ordering within each sales territory, let’s add a WITHIN GROUP() clause, wherein we can turn this into an ordered set function. That there’s the name of it, Maude!

SELECT
    SalesTerritory,
    STRING_AGG(StateProvinceCode, ',')
        WITHIN GROUP(ORDER BY StateProvinceCode)  AS StatesList
FROM Application.StateProvinces s
GROUP BY
    SalesTerritory;

All order, no province.

The WITHIN GROUP() clause, as we learned last time around, operates within each group, not each row. You might have noticed the GROUP BY clauses here, which I’ve generally been avoiding for window functions. That’s because the WITHIN GROUP() clause does not accept a PARTITION BY statement; it allows only an ordering statement, but that’s good enough for this case.

By the way, if you’re using other database platforms like DB2 or Oracle, this function is called LISTAGG(). In PostgreSQL and SQL Server, it’s STRING_AGG().

One of a Kind?

Here’s where I yank the rug out from under you and proclaim that, in fact, we have several ordered set functions in SQL Server: the statistical distribution functions! The way to tell an ordered set function from any other sort of function is that ordered sets use WITHIN GROUP(), sort of like how window functions use OVER(). You may recall from the prior post that statistical window functions like PERCENT_RANK(), CUME_DIST(), PERCENTILE_CONT(), and PERCENTILE_DISC() can also accept a WITHIN GROUP() clause, making them ordered set functions as well. The difference between those four and STRING_AGG() is that the statistical functions require an OVER() clause whereas STRING_AGG() does not want one.

Advertisement