CODEWITHSUNDEEP
sqlpostgresql
iskandarblue
iskandarblue

Reputation: 7526

Selecting records where change has occurred from previous row

I have a psql table called test with three attribute columns. The first type is an integer and the second is a character and the third is an integer

My goal is only to select the records where state has changed from A or B to C - in other words, where the current state is C and the previous state was either A or B, ordered by id and record number.

How would one write such a query in psql?

 id state record
  1     C      1
  1     A      2
  1     C      3
  1     A      4
  1     C      5
  1     A      6
  1     B      7
  2     C      8
  2     C      9
  2     C     10
  2     B     11
  2     C     12
  2     C     13
  2     C     14
  3     A     15
  3     C     16
  3     B     17
  3     A     18
  3     A     19
  3     A     20
  3     A     21
  3     C     22
  3     A     23
  3     B     24
  3     B     25

SELECTING on the above table would return something like this: 

id state record
  1     C      3
  1     C      5
  2     C     12
  3     C     16
  3     C     22

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method uses lag():

select t.*
from (select t.*, lag(t.state) over (partition by t.id order by t.record) as prev_state
      from t
     ) t
where t.state = 'C' and prev_state in ('A', 'B');

Another uses a self-join:

select t.*
from t join
     t tprev
     on t.id = tprev.id and t.record = t.record + 1
where t.state = 'C' and tprev.state in ('A', 'B')

Upvotes: 6

Related Questions