Feature Request - Ordered Set Functions - WITHIN GROUP Clause - by Itzik Ben-Gan

Status : 


Sign in
to vote
ID 728969 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 3/5/2012 5:35:12 PM
Access Restriction Public


Standard SQL defines a concept called ordered set function which is a set function that has ordering relevance. Ordered set functions can be applied to sets defined as groups in grouped queries. The standard provides a clause called WITHIN GROUP where you can provide the ordering specification. 

The standard defines some specific types of ordered set functions like inverse distribution functions and hypothetical set functions. However, what I find to be most important is product support for the general concept in the engine, including optimization, parsing, etc., to make it easy to add specific cases.

As of Microsoft SQL Server 2012 there’s no support yet for ordered set functions.

Here are a few examples for types of functions that would work well as ordered set functions:

1. Grouped String Concatenation
2. Ordered CLR Aggregates
3. Inverse Distribution Functions
4. Hypothetical Set Functions
5. Offset Functions

Due to a character limit in the description of the connect item there's no space for the full request here. The document http://tsql.solidq.com/OSF.doc provides the detailed feature request describing the concept as well as different examples where such support could be useful.
Sign in to post a comment.
Posted by Itzik Ben-Gan on 3/20/2017 at 11:23 PM
Starting with SQL Server vNext CTP 1.1, the STRING_AGG function supports an order clause (WITHIN GROUP), and this is reflected in the BOL article. Here's an example:

SELECT custid,
STRING_AGG(CAST(orderid AS VARCHAR(10)), ',')
    WITHIN GROUP(ORDER BY orderdate DESC, orderid DESC) AS orderids
FROM Sales.Orders
GROUP BY custid;

custid     orderids
----------- ---------------------------------------------------
1         11011,10952,10835,10702,10692,10643
2         10926,10759,10625,10308
3         10856,10682,10677,10573,10535,10507,10365
4         11016,10953,10920,10864,10793,10768,10743,10741...

So string concatenation is done. Let's hope to see items 2 - 5 in the list above supported as well in the future.
Posted by Martin Thøgersen on 11/20/2016 at 1:20 AM
SQL server vNext (2017/2018) implements STRING_AGG(col, seperator).
It doesn't mention ordering of the list by a single word. I find this very unambiguous.
It doesn't make sense to implement such a function without supporting ordering. Or at least documenting a fixed ordering.

Is the output even deterministic? Depends on the chosen query plan? Alphabetic? Which collation?
Posted by Arin T on 3/1/2016 at 12:36 PM
Agree- this would be very useful. In my current use case, I want to use my CLR String Concat aggregate function to act as a sort of hash for an unknown number of rows per grouping columns. The order matters for the concatenation in my case, because the order of the rows does not matter; by ensuring the same order of concatenation, two groups of the same rows in different orders will receive the same "hash" (concatenated string).

Why is this marked as CLOSED - Won't Fix??
Posted by Martin Thøgersen on 4/4/2015 at 2:27 PM
Why is this closed as won't fix?
Posted by Jerry Birchler on 1/23/2013 at 2:18 PM
You seem to have a lot of interest in ranking and ordering based on some of the suggestions you have posted. I use RANK and and DENSE_RANK on occasion to pick off the top most match within a group. It would be nice if somehow there was a hint option to optimize things like this. It sort of feels to me that something like an index that is sparsely populated might aid in optimizing. Or another way to imagine it would be a topmost placeholder key within a group, some way to define such a thing and call it an index. There may be some way to do this that I just don't know about. But, I like your suggestions. And, if there are ways to do this, I really would like to know.
Posted by Microsoft on 3/7/2012 at 11:12 AM
Hello Itzik,
Thanks for your feedback. We are already tracking this item internally but I will leave this one open on connect so customers can vote on it.

Umachandar, SQL Programmability Team