Hi everyone, I'm struggling to achieve a Postgres distinct on/order by query using the Exposed DSL... I'm looking to execute something like this

select distinct on
    (cats.name) cats
from 
    groups_cats
inner join
    cats
on
    groups_cats.cat_id = cats.id
where
    groups_cats.cat_id = 25
order by 
    cats.name, groups.created desc

I've managed to come up with this

GroupsCats
    .join(Cats, JoinType.INNER) { Cats.id eq GroupsCats.catId }
    .select { GroupsCats.catId eq catId }
    .orderBy(Cats.created to SortOrder.DESC)

But I can't figure out how to make the result distinct on a particular column (cat name in this case)... the

withDistinct()

method sounded hopeful but doesn't seem to work in the way that I need. If anyone has any ideas/suggestions I would really appreciate it.

Hey! My latest state is that it’s still not possible out-of-the-box with exposed but you can easily write this function yourself. This would be an example: https://github.com/JetBrains/Exposed/issues/500#issuecomment-543574151

What is withDistinct() doing that is unexpected?

The example from the issue you shared works perfectly @Kenneth Wußmann thank you 🙂 @Evan R. From what I can understand the

withDistinct()

method allows you to grab a single column but not retrieve entire rows with a distinct filter on one column if that makes sense.

Ah got it, I hadn’t seen “distinct on” before. You may want to read up on this issue then: https://github.com/JetBrains/Exposed/issues/500