Blog

SQL - Caution - the Distinct function may not return the records that you expect!

One thing that can catch out app builders is that the Distinct function is not delegable. This post illustrates this problem through an example that shows how the distinct function may not return all records that we expect.

If you want to show the distinct column values from a a table, it's very important to appreciate that from Power Apps, the Distinct function is not delegable. This means that it may not return all the values that we expect, and it is a problem that often catches out app builders. This post examines this behaviour in more detail.

Recreating the problem

To recreate the problem, let's create an app and set the row count limit to 10. Whenever we test an app, a great tip is to set the row count limit to a low value. This makes it more obvious if an app doesn't return the expected records.



We'll attempt to show the distinct country values form a table called Property. From SQL Server, we see that there are 207 distinct values.

The Distinct function is not delegable

From Power Apps, we call the Distinct function to return the distinct countries.
Distinct('[dbo].[Property]', Country).Result

The confusing thing here is that formula bar does not underline the formula in blue, or display any delegation warning messages. This can easily fool us into thinking that the formula is delegable and should return all expected values. As the screenshot beneath highlight, the output of this formula returns 10 rows only.



When we profile the call to SQL Server, we see that Power Apps attempts to resolve the distinct function by selecting the top 10 records only from the property table. From this, we can conclude that the distinct function is not delegable


How to correctly return distinct values from a table

The correct way to return distinct values in a delegable way is to build a view, which is covered in my post here.

http://powerappsguide.com/blog/post/sql-showing-distinct-values-the-correct-way

We define a view in SQL Server that returns the distinct countries and connect to this view from our app.

CREATE VIEW [dbo].[vw_DistinctCountries]
AS
SELECT DISTINCT Country
FROM
[dbo].[Property]

The view will be completely delegable, and will return the expected results.

Conclusion

App builders can be fooled into thinking that the Distinct function is delegable due to a lack of warning messages. It's important to appreciate that the Distinct function is not delegable, and that the best way to return distinct values from a SQL Server data sources is to use a view.
  •   Categories: 
  • sql
Previous
Next
Related posts