SELECT DISTINCT foo ON (bar)

[gfredericks]

I was trying to generate a SELECT DISTINCT ON (bar) foo recently and the only mechanism I could figure out was:

(-> (select :foo)
    (modifiers "DISTINCT ON (bar)")
    (sql/format))

;; => ["SELECT DISTINCT ON (BAR) foo "]

which feels like unintended usage, but at least works (at least because SQL isn't case-sensitive).

Is there a better way to do this, or a straightforward design as a new feature that I could add?

[jkk]

Right now this isn't supported, but could be if a syntax for it can be worked out.

One thought: create a new :select-distinct clause (see honeysql.format/format-clause) that takes two sets of fields: one for the "on" fields, and one for the other fields. E.g.,

{:select-distinct [[:bar] [:foo]]}

or, with a helper defined:

(select-distinct [:bar] [:foo])

[gfredericks]

Okay, that's helpful, thanks!

[jkk]

you can do this now:

(-> (select (sql/call :distinct-on :bar) :foo) (sql/format))
=> ["SELECT DISTINCT ON (bar), foo"]

[emidln]

SELECT DISTINCT ON (bar), foo isn't legal in PostgreSQL at least.