CODEWITHSUNDEEP
postgresql
Martin
Martin

Reputation: 11336

Select until row matches in Postgres

Given the following data structure:

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  1 |               1 | error | 2015-06-30 15:20:03.041045 | f
  2 |               1 | error | 2015-06-30 15:20:04.582907 | f
  3 |               1 | sent  | 2015-06-30 22:50:04.50478  | f
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

I want to retrieve the last messages with state='error' until the state contains something else.

It should return this:

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

Following this question and later this one, I ended up with this query below:

SELECT * from (select id, subscription_id, state, created_at,
   bool_and(state='error') 
   OVER (PARTITION BY state order by created_at, id) AS ok 
   FROM messages ORDER by created_at) m2 
   WHERE subscription_id = 1;

However, given that I added PARTITION BY state the query is simply ignoring all state which does not contain error and showing this instead: 

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  1 |               1 | error | 2015-06-30 15:20:03.041045 | f
  2 |               1 | error | 2015-06-30 15:20:04.582907 | f
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

How should the query be made in order to 'stop' after finding a different state and matching following the example described on the top only the ids 4 and 5?

Upvotes: 1

Views: 500

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

If I correctly understand, you need this:

select * from messages 
where
id > (select coalesce(max(id), 0) from messages  where state <> 'error')
and
subscription_id = 1

Assuming that id is unique (PK ?) column and higher id means latest record.

EDIT

Thats correct, as @Marth mentioned, probably you need add ... AND subscription_id = 1 in subquery

Upvotes: 2

Marth
Marth

Reputation: 24802

No need to PARTITION BY state, you want to SELECT rows where all rows afterward (in the created_at ASC order) are error, ie bool_and(state = 'error') is true:

SELECT * FROM (
  SELECT *,
         bool_and(state = 'error') OVER (ORDER BY created_at DESC, id) AS only_errors_afterward
  FROM sub
) s
WHERE only_errors_afterward
;
┌────┬─────────────────┬───────┬───────────────────────────────┬────┬───────────────────────┐
│ id │ subscription_id │ state │          created_at           │ ok │ only_errors_afterward │
├────┼─────────────────┼───────┼───────────────────────────────┼────┼───────────────────────┤
│  5 │               1 │ error │ 2015-07-01 22:50:02.356113+02 │ f  │ t                     │
│  4 │               1 │ error │ 2015-06-30 22:50:06.067279+02 │ f  │ t                     │
└────┴─────────────────┴───────┴───────────────────────────────┴────┴───────────────────────┘
(2 rows)

Edit: Depending on the expected result you might need a PARTITION BY subscription_id in the window function.

Upvotes: 2

Related Questions