News for hackers. Stuff that matters.
Re:Distinct on: a confusing, unique and useful feature in Po... by cosmie on Friday March 20, 2020 @02:25PM (#22640044)
One really useful call out is that it supports expressions, not just columns. You can create derived entities via case statements (think binning, entity normalization, etc) and leverage the super handy functionality against those derived entities.

It also allows you to use multiple entities/expressions in the distinct on, so you can pivot the logic between granularity levels. Which comes in handy when needing to pull rollups latest/first/biggest/smallest style rollups from varying perspectives.

Re:Distinct on: a confusing, unique and useful feature in Po... by rattray on Friday March 20, 2020 @05:39PM (#22641940)
This sounds pretty awesome. Anyone have any sample queries demonstrating this lying around?
Re:Distinct on: a confusing, unique and useful feature in Po... by cosmie on Friday March 20, 2020 @06:52PM (#22642547)
I threw together a Fiddle that demonstrates it here[1]. It shows the basic single column version, a multi-column distinct on, and an example that uses a case statement for a derived column. The only thing with the third example is that I don't believe you can alias the derived column within the distinct on field itself, so have to repeat the expression - once within the distinct on and again in the column list of the select statement.

[1] https://www.db-fiddle.com/f/7CCYFf14UtkuGYfGZAt5PY/3