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.
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.