Skip to content
Advertisement

PostgreSQL join table on json property and get oldest first from result with nulls first

I got 2 tables; domains and events. Im trying to create a query that returns a list of distinct domains that is ordered by the oldest events (for that domain) with nulls first and distinct domain.

Basically this query will do the job:

But the output is not distinct on ‘domain’. When using ‘distinct on’, it gives the wrong output where the timestamp (moment) is not the latest one for that domain in the events table:

Nor does this seem to work:

The following code simulates the database and the query that i need to have distinct on without having distinct modify the order of the rows:

(Oh, and as you will see, the parent needs to be null so it only selects top domains. But thats a simple ‘where’.)

How do I get this to work?

Advertisement

Answer

Have you considered using a row_number window function? Something like

The inner query uses row_number() OVER (PARTITION BY events.attributes->>'domain' ORDER BY moment ASC) rn to create a field that, for each events.attributes->>'domain' grouping, numbers things in order of events.moment. The outer query simply limits to the first one for each grouping and does the final ordering.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement