Allow sorting by a non-aggregated column

[orlando-colamatteo]

I think a great start for this would be to allow sorting by an integer column. It is easy to implement, size of the data is not a big issue, sorting is easy, and I suspect it would be the most common use-case. Support for other data types could be added later, although that might not even be necessary. Most use-cases that I have come across* where this feature is useful, the sort column is an integer already. More importantly, if no integer column is available, such a column can always be derived from any other column (or multiple columns!), with any data-type, using a ROW_NUMBER() over (ORDER BY ...) clause. This also has the advantage of letting us leverage the sorting capabilities of SQL Server, thus avoiding the wrinkles with implementing our own data-type specific sorting Also, it enables us to sort by multiple columns, and use ascending/descending sorts per columns, which would be extremely hard to implement ourselves, all for free, while keeping our implementation very clean. ROW_NUMBER() also performs extremely well in my experience.

A first implementation would be easy enough to make by adapting one of the current implementations. By using a Dictionary, and requiring that we do not receive duplicate int keys in the sort column, we are almost there already (although such a requirement might be up for discussion). We can then feed this Dictionary into a SortedDictionary, like GROUP_CONCAT_DS does.

Attachments

GROUP_CONCAT_DSC.cs

This work item was migrated from CodePlex

CodePlex work item ID: '912' Assigned to: 'opcthree' Vote count: '3'

[orlando-colamatteo]

[Edalb@10/13/2012] I created a first implementation for this called GROUP_CONCAT_DSC (for Delimiter, Sort Column). The name should probably be changed later, because it is a little cryptic.

[orlando-colamatteo]

[UnknownUser@2/14/2013]

[orlando-colamatteo]

[UnknownUser@11/9/2013]

[orlando-colamatteo]

[UnknownUser@11/9/2013]

[orlando-colamatteo]

[UnknownUser@4/6/2014] ...going to move forward with developing the concept. I looked into this a bit more and was curious about the limitation you mentioned to only accept one RANK value, which I think I would prefer to name SORT_KEY to avoid confusion with RANK which introduces the concepts of regular RANK and DENSE_RANK for some. Is limiting the user to a unique set of SORT_KEYs something you think is important to preserve the integrity of the results or something you did not see as important enough to implement to satisfy your use cases? I could see value in allowing more than one SORT_KEY value but would likely document it as “consistent sorting of values with the same SORT_KEY is not guaranteed.” I was thinking of using the exact same code as GROUP_CONCAT_DS but instead of storing it in a Dictionary<int, string> I was thinking of using KeyValuePair or creating a new struct, something like SortablePair with two properties, a string for the value and an int for the sort key. When the SortedDictionary was created I would also implement a new Comparer that would inspect the sort key portion of the struct.

[orlando-colamatteo]

[UnknownUser@4/6/2014] Associated with changeset 34176: add GroupConcatAltSorted; refactor comparers into new namespace; adopt Pascal-case naming convention

[orlando-colamatteo]

[UnknownUser@5/21/2015]

[orlando-colamatteo]

[alendar@10/16/2015] There was a bug in GroupConcatAltSorted. I changed it in my copy from:

// iterate over the SortedDictionary
                foreach (KeyValuePair<KeyValuePair<string, string>, int> item in sortedValues)
                {
                    KeyValuePair<string, string> key = item.Key;
                    for (int value = 0; value < item.Value; value++)
                    {
                        returnStringBuilder.Append(key.Key);
                        returnStringBuilder.Append(",");
                    }
                }
                return returnStringBuilder.Remove(returnStringBuilder.Length - 1, 1).ToString();

To:

// iterate over the SortedDictionary
                foreach (KeyValuePair<KeyValuePair<string, string>, int> item in sortedValues)
                {
                    KeyValuePair<string, string> key = item.Key;
                    for (int value = 0; value < item.Value; value++)
                    {
                        returnStringBuilder.Append(key.__Value__);
                        returnStringBuilder.Append(",");
                    }
                }
                return returnStringBuilder.Remove(returnStringBuilder.Length - 1, 1).ToString();

[orlando-colamatteo]

[opcthree@11/1/2015] Thanks for the fix alendar.

[orlando-colamatteo]

[UnknownUser@3/1/2017]

[cjba850]

Did this ever get put into the bundle? I'm working on something, and the code above is via the old codeplex site (so I can't seem to find it).

I have an index/sequence column already, so if I could feed in the sequence to the Group_concat_d, that would be helpful.

Thanks though! Really loving what it does do already.

EDIT: Managed to find the old code buried in the archived site, but not so clear on turning the .cs into the Aggregate function so I can call it via SQL.

Thanks!

[JannemanDev]

+1

[Halofreak1990]

Did this ever get put into the bundle? I'm working on something, and the code above is via the old codeplex site (so I can't seem to find it).

I have an index/sequence column already, so if I could feed in the sequence to the Group_concat_d, that would be helpful.

Thanks though! Really loving what it does do already.

EDIT: Managed to find the old code buried in the archived site, but not so clear on turning the .cs into the Aggregate function so I can call it via SQL.

Thanks!

@cjba850 the code is meant to be placed in a C# project and added to your installation of SQL Server as a SQLCLR assembly.

[tsalomak]

Well, now you can't find it in codeplex anymore -- and this code never made it into github at all?

[Tabery]

Please upload GROUP_CONCAT_DSC.cs, codeplex is already finished. Even better would be to add a function to GroupConcatInstallation.sql.

[orlando-colamatteo]

Please upload GROUP_CONCAT_DSC.cs, codeplex is already finished. Even better would be to add a function to GroupConcatInstallation.sql.

@Tabery, The concept was developed in a feature branch: GroupConcat/GroupConcatAltSorted.cs

It probably needs a bit of testing as it's been a while since I touched the code.