CODEWITHSUNDEEP
sqlpostgresqlselect
sh4rkyy
sh4rkyy

Reputation: 402

Select last row where given value changed

I want to select last row (by mod_date) where column new_status is different from previous entry for given object_id.

At first I tried with row_number but not made it, later I came up with lead/lag functions and I think I'm closer to solution but still not ideal results.

Here's the code and the fiddle: https://www.db-fiddle.com/f/kS8SAi2WsAjfFLomd7t2it/0

CREATE TABLE changes
(object_id integer,
 new_status smallint,
 comment text,
 mod_date timestamp);
 
INSERT INTO changes
 VALUES
 (1001, 0, null, '2020-06-01 12:01'),
  (1001, 1, 'XYZ', '2020-06-01 12:05'),
   (1001, 1, 'YZX', '2020-06-01 12:11'),
    (1002, 1, 'XYZ', '2020-06-01 13:21'),
     (1002, 1, 'AAA', '2020-06-01 13:25'),
      (1002, 0, 'BCA', '2020-06-01 14:11'),
       (1003, 1, 'AXX', '2020-06-01 14:12'),
        (1003, 0, 'YZX', '2020-06-01 14:13'),
         (1003, 0, 'YYY', '2020-06-01 14:17');


SELECT object_id, min(mod_date), new_status FROM (
    SELECT 
      object_id
    , mod_date 
    , new_status
    --, row_number() over (partition BY object_id ORDER BY mod_date desc) rn
    , lag(new_status) OVER (partition by object_id ORDER BY mod_date desc) as next_status

    FROM changes
    ORDER BY 1)x
WHERE new_status = next_status 
OR next_status is null
GROUP BY 1,3

The output for 1001, and 1003 is fine, for 1002 it should be row with status 0.

Appreciate any help and suggestions!

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you want:

select distinct on (object_id) c.*
from (select c.*,
              lag(new_status) over (partition by object_id order by mod_date) as prev_ns
      from changes c
     ) c
where prev_ns is distinct from new_status
order by object_id, mod_date desc;

Here is a db<>fiddle.

Upvotes: 3

Related Questions