Select minimum value by value transition in PostgreSQL

Question

I have a table with some data related to articles in a store:

Article     Date         Stored
A           22/08/2019   True
A           23/08/2019   True
A           24/08/2019   True
A           25/08/2019   False
A           26/08/2019   True
A           27/08/2019   True
A           28/08/2019   False
A           29/08/2019   False
A           30/08/2019   True

What I'd like to get is the minimum date where A has value Stored=True and value Stored=False:

Article     Date         Stored
A           22/08/2019   True
A           25/08/2019   False

But, let's suppose that, for article A, there is data where Stored=True after Stored=False (case of date 26/08/2019). In this situation, I'd like to show this minimum value too. To sum up, what I want is the minimum date value each time that a transition happens (this is, Stored passes from True to False or from False to True):

So my final result would be this:

Article     Date         Stored
A           22/08/2019   True
A           25/08/2019   False
A           26/08/2019   True
A           28/08/2019   False
A           30/08/2019   True

Any help would be appreciated :)

Barbaros Özhan · Accepted Answer · 2019-09-24 18:40:33Z

2

It's a typical gaps-and-islands problem. You can use row_number() analytical function :

select article, min(date) as date, stored
  from (
        select *,
               row_number() over (partition by article, stored order by date) as rn1,
               row_number() over (partition by article order by date) as rn2
          from tab 
       ) t
 group by article, stored, rn1 - rn2
 order by date;

Demo

answered Sep 24, 2019 at 18:40

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

belfastcowboy24 · Accepted Answer · 2019-09-24 18:24:12Z

1

I think I came up with an answer:

select article,date,stored from(
select
article,date,stored,lag(stored) over (order by date) stored_previous_value from my_table
)q1
where stored!=stored_previous_value or stored_previous_value is null

answered Sep 24, 2019 at 18:24

belfastcowboy24

3571 gold badge3 silver badges9 bronze badges

1 Comment

Barbaros Özhan Over a year ago

your solution is also good.

Collectives™ on Stack Overflow

Select minimum value by value transition in PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related