3
Vote

Allow sorting by a non-aggregated column

description

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.

file attachments

comments

Edalb wrote Oct 13, 2012 at 9:19 PM

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.

wrote Feb 14, 2013 at 7:20 PM

wrote Nov 9, 2013 at 12:45 PM

wrote Nov 9, 2013 at 12:45 PM

wrote Apr 6, 2014 at 10:30 AM

...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.

wrote Apr 6, 2014 at 11:48 AM

Associated with changeset 34176: add GroupConcatAltSorted; refactor comparers into new namespace; adopt Pascal-case naming convention

wrote May 21, 2015 at 12:14 PM

alendar wrote Oct 16, 2015 at 3:31 PM

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();

opcthree wrote Nov 2, 2015 at 4:35 AM

Thanks for the fix alendar.

wrote Mar 1 at 9:49 AM