Ordering of records with distinct and order_by

Questions / Help
1

I have this query in which order_by and distinct fields are present but both have different fields The query is:

    from(h in model, order_by: [desc: h.name], distinct: [asc: h.id]

The records returned are sorted by distinct and not by order_by.
I want records to be sorted by name and not id.
I want each record to be distinct based on its primary key, but also sorted by the name field
Is there any workaround this?
Thanks.

2

Short version

By definition a primary key is unique, your distinct key does no filtering and should be omitted. Leaving you with the order_by key to sort the rows the way you are after.

Longer version

From the docs you can see that distinct: [asc: h.id] produces a DISTINCT ON query where the column is prepended to any order_by option.

If supported by your database, you can also pass query expressions to distinct and it will generate a query with DISTINCT ON. In such cases, distinct accepts exactly the same expressions as order_by and any distinct expression will be automatically prepended to the order_by expressions in case there is any order_by expression.

You can see the reason why DISTINCT ON is related to ORDER BY:

With DISTINCT ON , You tell PostgreSQL to return a single row for each distinct group defined by the ON clause. Which row in that group is returned is specified with the ORDER BY clause.

DISTINCT is always about selecting one row from potentially many rows that are the same. DISTINCT ON is about selecting one row from potentially many that are very similar and using ORDER BY to determine which one of those rows to select.

2 Likes