I'm curious about that aggregate and a bit scared by the different ORDER BYs, it looks like you the function would be unable to re-use index reads for other column values.
Do you have much experience with Postgres WINDOW[1] expressions? That might be a useful tool for you even if it takes a bit of time to fully grok it. I don't use WINDOW often but one place I found it was insanely useful was for supplying a tabular page with aggregate data points outside of the data currently being paged to the client - we have a query that returns row information in a paged manner but the query driving it both narrows the row scope to fit the page size request while also using WINDOW functions over the un-partitioned data to calculate a few business useful averages.
> it looks like you the function would be unable to re-use index reads for other column values.
PostgreSQL would have to do a separate sort for each different ORDER BY, yes.
Also, it can't use the the index to scan only some values as it could for DISTINCT ON, because the aggregate is opaque. That may or may not actually matter though.
Another option (though more verbose) is LATERAL plus LIMIT 1.
> Do you have much experience with Postgres WINDOW expressions?
Yes.
SELECT DISTINCT
a,
first_value(b) OVER (PARTITION BY a ORDER BY x),
first_value(c) OVER (PARTITION BY a ORDER BY x DESC)
FROM table
One downside is that I can't use FILTER with those as with aggregates, though sometimes you can hack something together with PARTITION/ORDER BY/RANGE
However...
(1) AFAIK it's not default in MySQL (version 5.7.5+). You have to disable ONLY_FULL_GROUP_BY to get this to work.
(2) Often you will care about which one is chosen. It would have been more useful if MySQL had let you influence this with ORDER BY.
(3) I create a current_value() aggregate which gives me even more flexibility than either approach for selecting the value.