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

Question

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...

date is later than a specific date
and flag = 0
and there is NO other row of a later date and with flag = 1 for the same user

...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?

Please provide sample data and desired results. Your current query would also help. — Gordon Linoff
– Gordon Linoff, Commented May 17, 2018 at 14:52

Gordon Linoff · Accepted Answer · 2018-05-17 14:54:00Z

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.

Thank you, this looks very promising! Could you explain what the having line in your second example does? I don't understand how the min/max functions work in combination with case.
@Hatch . . . I would suggest that you try min(case . . .) and max(case . . .) in some queries on your data (in the select), so you will see what happens.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related