Support for DISTINCT(column)

[killdashnine]

It would be nice to be able to use SELECT DISTINCT (column) using slick-pg so one could perform queries like:

SELECT DISTINCT ON (customer)  id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id

[tminglei]

Hi @killdashnine, this is a feature that I didn't noticed before, and seems very useful sometime. I'll try to implement it.

Will keep you updated!

[tminglei]

This feature can't be implemented without support from slick. Let's watch a related slick issue slick/slick#902's progress first.

[kareblak]

Not much happening on that one it seems. Are there any ways to get around this issue, with

purchases.sortBy(_.total).groupBy(_.customer).flatMap(_._2.take(1))

or something? Will this even work?

[tminglei]

This expr won't work, because seems it's generating sql like below

select col1, col2, ... limit 1 /* something like this */
from purchase
group by customer
order by total

And I haven't gotten other ways.

[teldosas]

I now it's too late now but for future reference this would work

import com.github.tminglei.slickpg.window.PgWindowFuncSupport.WindowFunctions._

purchases.map( p => (p, rowNumber() :: Over.partitionBy(p.customer)).subquery.
                filter {
                  case (p, r) => r <= 1
                } map {
                  case (p, r) => p
               } sortBy(p => (p.customer, p.total.desc, p.id))

[teldosas]

Any news on the distinctOn?