CODEWITHSUNDEEP
sqlpostgresql
Hatch
Hatch

Reputation: 605

How to query a table while comparing column values of two different rows?

Suppose I have a single table foo, with relevant columns flag, date and username.

I am struggling to come up with a PostgreSQL query that does the following:

Select all rows where...

...grouped by username.

The problem is very much the third requirement in the list above. I have tried building the query using subqueries with EXCEPT, WHERE NOT EXISTS, WITH and LATERAL, but I always run into a dead end when the dates of the subqueries must be compared to each other and I cannot reference them.

Is this possible within a single SQL statement?

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

This looks a lot like not exists:

select t.*
from t
where date > ? and flag = 0 and
     not exists (select 1
                 from t t2
                 where t2.username = t.username and t2.flag = 1 and t2.date > t.date
                );

If you just want user names meeting the condition, conditional aggregation should suffice:

select username
from t
where date > ?
group by username
having min(case when flag = 0 then date end) < max(case when flag = 1 then date end);

This is saying that the last flag = 1 date is later than the earliest flag = 0.

Upvotes: 1

Related Questions