Wappler 5.3.1
PG SQL 13
Node.JS 14
for the Wappler Paged Query step, if we have DISTINCT enabled and have sortable headers we get following error when we try to sort by any field from client side and request is sent with the sort param:
for SELECT DISTINCT, ORDER BY expressions must appear in select list
if we remove DISTINCT, sorting request from client side works as expected.
please fix.
Make sure the columns you order on are in the output.
How SQL DISTINCT and ORDER BY are Related – Java, SQL and jOOQ.
A simple example why DISTINCT is giving problems combined with ORDER BY
| userid | name | age |
|---|---|---|
| 1 | adam | 48 |
| 2 | charles | 42 |
| 3 | adam | 35 |
Getting all names ordered by age
SELECT name FROM users ORDER BY age
returns:
| name |
|---|
| adam |
| charles |
| adam |
Now trying to get all names ordered by age but without duplicates
SELECT DISTINCT name FROM users ORDER BY age
this returns the error for SELECT DISTINCT, ORDER BY expressions must appear in select list.
Why? With the result from the previous query you have adam 2 times, each with a different age. How would you sort them on age when you only return a single of them, does it come before or after charles.
Hope this sample makes clear why it does give this error.
the order by clause is initiated from client side - form the sortable headers.
the data populated in the table with sortable headers is what the paged query returns.
so essentially, what you explain is not really possible in our situation coz the column visible is what is being sorted.
but we are using aliases in our paged query - will recheck if that is causing the problem.
Then it probably is the aliases that you use, not sure if you can use the alias in the order by clause.