Support DISTINCT ON

[dmoiseenko]

DISTINCT ON is a really powerful Postgres feature to perform aggregation-like(pick first in group). It can be used in plain SELECT queries without window function or GROUP BY.
It will be great to see support of DISTINCT ON since Hasura has excellent support of ORDER BY.

e.g.
We need to get the cheapest fruits in groups by color(see sudo SQL<=>GraphQL below)

SELECT DISTINCT ON
  (color)
  color,
  name,
  price
FROM
  fruit
ORDER BY price;

query {
  fruit(distinct_on: [color], order_by: {price: asc}) {
    color
    name
    price
  }
}

Postgres documentation https://www.postgresql.org/docs/11/sql-select.html#SQL-DISTINCT
Usage example https://medium.com/statuscode/the-many-faces-of-distinct-in-postgresql-c52490de5954

[0x777]

@dmoiseenko Can you try this out on this PR build: https://hge-ci-pull-1099.herokuapp.com/

{
  product(distinct_on: color order_by: {color: asc price:desc}) {
    id
    price
    color
  }
}

Note that we force the Postgres restriction that the DISTINCT ON column(s) must match the leftmost ORDER BY column(s).

[dmoiseenko]

@0x777
WOW! Works great!

[anonrose]

Thanks for the insight @0x777

[cleivson]

Hi, I know this has been closed already, but is it supposed to work with multiple columns like the postgres query does? I can only use it for a single column

[rakeshkky]

Hi @cleivson, please note that this issue was related to Postgres. By the time this was closed, we only support single Postgres source (version 1.x). Thank you.

[jflambert]

Hi, I know this has been closed already, but is it supposed to work with multiple columns like the postgres query does? I can only use it for a single column

you use [] to specify multiple columns. You can't do it with the graphiql editor.

Hi @cleivson, please note that this issue was related to Postgres. By the time this was closed, we only support single Postgres source (version 1.x). Thank you.

That's not at all what he was asking @rakeshkky he wanted to know about multiple columns in a distinct clause.