1

I have a table like below.

date         id    event      keyword   val     pattern_id()
2017-08-01   001   triggerX   abc       (null)  1
2017-08-01   001   triggerY   (null)     3      1
2017-08-01   009   triggerX   cde       (null)  2
2017-08-01   010   triggerX   ghi       (null)  3
2017-08-01   010   triggerX   ghi       (null)  3
2017-08-01   010   triggerX   ghi       (null)  3
2017-08-01   010   triggerY   (null)     1      3
(list continues..)

Event triggerX will be followed by triggerY (not vice versa). There is a chance where there is only triggerX (no triggerY) like id 009. However, there is no chance triggerY only.

What I'd like to do is following.

For example of id 001, I'd like to merge triggerX keyword column and triggerY val column into one row.

For example of id 010. It has four events, but I just need last two events (last triggerX and Y) and merge keyword column and val column. That will give me below.

date        id   keyword   val
2017-08-01  001  abc       3
2017-08-01  010  ghi       1

Could you help me to figure out how to construct SQL to get the result like above?

kazzi
  • 524
  • 9
  • 33
K.K.
  • 415
  • 1
  • 5
  • 13

1 Answers1

1

I think this does what you want:

select date_id, id, max(event), max(keyword)
from t
where keyword in ('triggerX', 'triggerY')
group by date_id, id
having count(distinct keyword) = 2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786