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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


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

Description

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 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 Umachandar [MSFT] 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