Home Dashboard Directory Help
Search

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


Status: 

Active


105
0
Sign in
to vote
Type: Suggestion
ID: 728969
Opened: 3/5/2012 5:35:12 PM
Access Restriction: Public
0
Workaround(s)
view

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.
Details
Sign in to post a comment.
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
Sign in to post a workaround.