CODEWITHSUNDEEP
sqlpostgresql
user2666373
user2666373

Reputation: 11

PostgreSQL rank query

I have data in postgreSQL table as follows,

pkid  id  timestamp              isactive
-----------------------------------------
   1  1   "2013-08-08 10:10:10"  0
   2  1   "2013-08-08 10:11:10"  0
   3  1   "2013-08-08 10:12:10"  0
   4  1   "2013-08-08 10:13:10"  1
   5  1   "2013-08-08 10:14:10"  1
   6  1   "2013-08-08 10:15:10"  1
   7  1   "2013-08-08 10:16:10"  1
   8  1   "2013-08-08 10:17:10"  1
   9  1   "2013-08-08 10:18:10"  0
  10  1   "2013-08-08 10:19:00"  0
  11  2   "2013-08-08 09:10:10"  0

I want to get an query to get first record when it changes from active to inactive state or vice versa, eg for each id,

   1  1   "2013-08-08 10:10:10"  0
   4  1   "2013-08-08 10:13:10"  1
   9  1   "2013-08-08 10:18:10"  0
  11  2   "2013-08-08 09:10:10"  0

I tried using rank() but it assign rank value across the active/inactive, ie rank() (partition by id,isactive order by timestamp)

 pkid id  timestamp             isactive  rank
 ----------------------------------------------
    1 1  "2013-08-08 10:10:10"   0        1
    2 1  "2013-08-08 10:11:10"   0        2
    3 1  "2013-08-08 10:12:10"   0        3
    4 1  "2013-08-08 10:13:10"   1        1
    5 1  "2013-08-08 10:14:10"   1        2
    6 1  "2013-08-08 10:15:10"   1        3
    7 1  "2013-08-08 10:16:10"   1        4
    8 1  "2013-08-08 10:17:10"   1        5
    9 1  "2013-08-08 10:18:10"   0        4
   10 1  "2013-08-08 10:19:00"   0        5
   11 2  "2013-08-08 09:10:10"   0        1

i would like to see the result as,

pkid  id timestamp              isactive rank
---------------------------------------------
   1  1  "2013-08-08 10:10:10"  0         1
   2  1  "2013-08-08 10:11:10"  0         2
   3  1  "2013-08-08 10:12:10"  0         3
   4  1  "2013-08-08 10:13:10"  1         1
   5  1  "2013-08-08 10:14:10"  1         2
   6  1  "2013-08-08 10:15:10"  1         3
   7  1  "2013-08-08 10:16:10"  1         4
   8  1  "2013-08-08 10:17:10"  1         5
   9  1  "2013-08-08 10:18:10"  0         1
  10  1  "2013-08-08 10:19:00"  0         2
  11  2  "2013-08-08 09:10:10"  0         1

i can then pick all rank ==1 and get the timestamp when the state changed.

Upvotes: 1

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do this is by using the lag() function:

select t.*
from (select t.*,
             lag(isactive) over (partition by id order by timestamp) as prevIsActive
      from t
     ) t
where prevIsActive is NULL or prevIsActive <> IsActive;

You can only do it the way that you suggested if the state only goes in one direction -- from inactive to active or vice versa. Your method combines all the actives and inactives together for an id, resulting in the continuous numbering.

Upvotes: 1

Related Questions