I have this query in which
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
I want records to be sorted by
name and not
I want each record to be distinct based on its primary key, but also sorted by the name field
Is there any workaround this?
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.
From the docs you can see that
distinct: [asc: h.id] produces a
DISTINCT ON query where the column is prepended to any
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
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.