cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BrianHFASPS
Level 8

Different results when sorting Collection vs. SQL View?

I am trying to use more local Collections and less data pull direct from SQL server. To that end I am swapping a SQL call for collection refrence. However I am seeing very different results when I do a sort on a collection vs. SQL View?

Here is my collection statement:

Refresh('[dbo].[Pickup]');ClearCollect(CarNumberCollection, '[dbo].[Pickup]')

Then my two gallery item filters are:

Sort(Sort(Filter(CarNumberCollection, (StartsWith('Student Last Name', FilterTextBoxName.Text) || Value(FilterTextBoxName.Text) = 'Car #'), 'Car Order' = 0), Weight, SortOrder.Descending), 'Car #', Ascending)
Sort(Sort(Filter('[dbo].[Pickup]', (StartsWith('Student Last Name', FilterTextBoxName.Text) || Value(FilterTextBoxName.Text) = 'Car #'), 'Car Order' = 0), Weight, SortOrder.Descending), 'Car #', Ascending)

The only difference is the table. I get totally different results though? Does Sort work differently on a Collection?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Different results when sorting Collection vs. SQL View?

When you have a Sort expression inside another Sort expression, you may lose the sorting that was done in the inner sort expression.

For example, if you have this table:

/------------------\
| 'Car #' | Weight |
|---------|--------|
| 01      |  33    |
| 02      |  32    |
| 03      |  33    |
| 04      |  34    |
| 05      |  33    |
| 06      |  34    |
| 07      |  32    |
\------------------/

And you use the expression 

Sort(Sort(coll, Weight, SortOrder.Descending), 'Car #', Ascending)

Semantically, it's saying to first sort the collection by weight, in descending order, and then sort the result by 'Car #', in ascending order. That will cause the first sort to be ignored - since there are no duplicate 'Car #' values, that will be the primary sort key.

I believe you wanted the sort to be done by weight, and in case of ties, use the 'Car #' as a secondary sort key. To do that, you can have some expression that will do that, like this (which assumes that 'Car #' has values less than 10000):

Sort(coll, Weight * 10000 - 'Car #', Descending)

Or you can use the SortByColumns function, that allows you to choose different columns for primary and secondary (and tertiary, and so on) sort keys:

SortByColumns(coll; "Weight"; Descending; "Car_x0020__x0023_"; Ascending)

Now, why you see a difference in SQL is that PowerApps tries to delegate as much as possible to the data source, and Sort (and Filter) are delegated to SQL Server. And the semantics of multiple Sort calls is different when it is delegated to the server side - the SQL Server will try to use both keys as primary/secondary for sorting. One could argue that the SQL Server case is incorrect (not necessarily a problem in SQL Server, more in how PowerApps is communicating with it), but if you use the SortByColumns or a single Sort expression in your app you should get the results you want.

Hope this helps!

1 REPLY 1
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Different results when sorting Collection vs. SQL View?

When you have a Sort expression inside another Sort expression, you may lose the sorting that was done in the inner sort expression.

For example, if you have this table:

/------------------\
| 'Car #' | Weight |
|---------|--------|
| 01      |  33    |
| 02      |  32    |
| 03      |  33    |
| 04      |  34    |
| 05      |  33    |
| 06      |  34    |
| 07      |  32    |
\------------------/

And you use the expression 

Sort(Sort(coll, Weight, SortOrder.Descending), 'Car #', Ascending)

Semantically, it's saying to first sort the collection by weight, in descending order, and then sort the result by 'Car #', in ascending order. That will cause the first sort to be ignored - since there are no duplicate 'Car #' values, that will be the primary sort key.

I believe you wanted the sort to be done by weight, and in case of ties, use the 'Car #' as a secondary sort key. To do that, you can have some expression that will do that, like this (which assumes that 'Car #' has values less than 10000):

Sort(coll, Weight * 10000 - 'Car #', Descending)

Or you can use the SortByColumns function, that allows you to choose different columns for primary and secondary (and tertiary, and so on) sort keys:

SortByColumns(coll; "Weight"; Descending; "Car_x0020__x0023_"; Ascending)

Now, why you see a difference in SQL is that PowerApps tries to delegate as much as possible to the data source, and Sort (and Filter) are delegated to SQL Server. And the semantics of multiple Sort calls is different when it is delegated to the server side - the SQL Server will try to use both keys as primary/secondary for sorting. One could argue that the SQL Server case is incorrect (not necessarily a problem in SQL Server, more in how PowerApps is communicating with it), but if you use the SortByColumns or a single Sort expression in your app you should get the results you want.

Hope this helps!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 43 members 4,081 guests
Please welcome our newest community members: