CODEWITHSUNDEEP
hadoophive
Ikshwaku
Ikshwaku

Reputation: 35

query to capture rows if value changes in a column

I need to capture those specific rows where there is a change in value of a specific column like "Toggle"

I have the below data :

ID  ROW Toggle  Date
661 1   1   2017-03-01
661 2   1   2017-03-02
661 3   1   2017-03-03
661 4   1   2017-03-04
661 5   1   2017-03-05
661 6   1   2017-03-06
661 7   1   2017-03-07
661 8   1   2017-03-08
661 9   1   2017-03-09
661 10  1   2017-03-10
661 11  1   2017-03-11
661 12  1   2017-03-12
661 13  1   2017-03-13
661 14  1   2017-03-14
661 15  1   2017-03-15
661 16  1   2017-03-16
661 17  1   2017-03-17
661 18  1   2017-03-18
661 19  1   2017-03-19
661 20  1   2017-03-20
661 21  1   2017-03-21
661 22  1   2017-03-22
661 23  1   2017-03-23
661 24  1   2017-03-24
661 25  1   2017-03-25
661 26  1   2017-03-26
661 27  1   2017-03-27
661 28  1   2017-03-28
661 29  1   2017-03-29
661 30  1   2017-03-30
661 31  1   2017-03-31
661 32  1   2017-04-01
661 33  1   2017-04-02
661 34  1   2017-04-03
661 35  1   2017-04-04
661 36  1   2017-04-05
661 37  0   2017-04-06
661 38  0   2017-04-07
661 39  0   2017-04-08
661 40  0   2017-04-09

Query used :

select b.id, b.ROW b.tog, b.ts
from
(select id, ts, tog,
ROW_NUMBER() OVER (order by ts ASC) as ROW
from database.source_table
where id = 661
) b

Can anyone help me with the query so that I can fetch only 1st and 37th row from source table?

Upvotes: 1

Views: 427

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Use row_number() + filter. This query will output 1st and 37th row:

select b.id, b.ROW, b.toggle, b.date
from
(select id,  date, toggle,
ROW_NUMBER() OVER (partition by id, toggle order by date ASC) as rn,
ROW_NUMBER() OVER (partition by id order by date ASC) as ROW
from test_table
where id = 661
) b
where rn=1
order by date asc

Result: OK

661     1       1       2017-03-01
661     37      0       2017-04-06
Time taken: 192.38 seconds, Fetched: 2 row(s)

Upvotes: 1

Related Questions