CODEWITHSUNDEEP
sqlpostgresql
sannan14
sannan14

Reputation: 79

Sql query to print all the rows if row value changes from previous

I have a table like

 id device_id  time   mode   sign        plan  
  1   1001     9.00     1      1          A
  2   1001     9.01     2      1          A
  3   1001     9.02     3      1          B
  4   1001     9.03     4      1          B
  5   1001     9.04     5      1          A

I want to writa an sql query that prints all the rows if plan value changes from previous row. so that the output should be 

 1   1001     9.00     1      1          A
 3   1001     9.02     3      1          B
 5   1001     9.04     5      1          A

Is there any way to do this? This is large table with lot of other columns and >100k of data. Joining with the same table doesn't work as it takes too long to execute.

I tried with this.

SELECT device_id, time ,  mode ,  sign  ,plan
FROM table where id IN
(SELECT MIN(id) FROM table GROUP BY plan)

But this gives only

 1   1001     9.00     1      1          A
 3   1001     9.02     3      1          B

Can anyone suggest any improved version of this query which gives

 1   1001     9.00     1      1          A
 3   1001     9.02     3      1          B
 5   1001     9.04     5      1          A

Upvotes: 1

Views: 412

Answers (1)

Andomar
Andomar

Reputation: 238058

You could use the lag window function:

select  *
from    (
        select  lag(plan) over (order by id desc) as prev_plan
        ,       *
        from    YourTable
        ) yt
where   plan <> prev_plan
        or prev_plan is null

Example at regtester.

Upvotes: 2

Related Questions