update older records in each group There is a set of data, like


| id | serial | version             | is_deleted |
--------------------------------------------------
| 10 | AAAAAA | 2019-04-14 15:28:08 | 0          |
| 22 | AAAAAA | 2019-03-04 15:28:08 | 0          |
| 13 | AAAAAA | 2019-02-10 15:28:08 | 0          |
| 40 | BBBBBB | 2019-04-17 15:28:08 | 0          |
| 27 | BBBBBB | 2019-02-20 15:28:08 | 0          |
| 17 | CCCCCC | 2019-03-04 15:28:08 | 0          |
| 35 | CCCCCC | 2019-01-01 15:28:08 | 0          |


I want to mark all older entries in each group as deleted and to have following data as result


| id | serial | version             | is_deleted |
--------------------------------------------------
| 10 | AAAAAA | 2019-04-14 15:28:08 | 0          |
| 22 | AAAAAA | 2019-03-04 15:28:08 | 1          |
| 13 | AAAAAA | 2019-02-10 15:28:08 | 1          |
| 40 | BBBBBB | 2019-04-17 15:28:08 | 0          |
| 27 | BBBBBB | 2019-02-20 15:28:08 | 1          |
| 17 | CCCCCC | 2019-03-04 15:28:08 | 0          |
| 35 | CCCCCC | 2019-01-01 15:28:08 | 1          |


What SQL query can do that? (PostgreSQL)

P.S. in case it is much harder with groups having 3 and more items, I would be glad to have even SQL which processes data with groups having 2 items.

Schema and data


CREATE TABLE public.items
(
    id serial,
    name character varying(10) NOT NULL,
    version timestamp without time zone NOT NULL,
    is_deleted boolean default false,
    PRIMARY KEY (id)
)   

INSERT INTO public.items(
    id, name, version, is_deleted)
    VALUES
        (10, 'AAAAAA', '2019-04-14 15:28:08', false)
        (22, 'AAAAAA', '2019-03-04 15:28:08', false),
        (13, 'AAAAAA', '2019-02-10 15:28:08', false),
        (40, 'BBBBBB', '2019-04-17 15:28:08', false),
        (27, 'BBBBBB', '2019-02-20 15:28:08', false),
        (17, 'CCCCCC', '2019-03-04 15:28:08', false),
        (35, 'CCCCCC', '2019-01-01 15:28:08', false);
Test table:

create table dbase234936 ( 
id integer,
serial varchar(7),
version timestamp,
is_deleted integer
);


Test data:

insert into dbase234936 values ( 10 , 'AAAAAA' , '2019-04-14 15:28:08', 0);
insert into dbase234936 values ( 22 , 'AAAAAA' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 13 , 'AAAAAA' , '2019-02-10 15:28:08', 0);
insert into dbase234936 values ( 40 , 'BBBBBB' , '2019-04-17 15:28:08', 0);
insert into dbase234936 values ( 27 , 'BBBBBB' , '2019-02-20 15:28:08', 0);
insert into dbase234936 values ( 17 , 'CCCCCC' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 35 , 'CCCCCC' , '2019-01-01 15:28:08', 0);


Query:

with windows as ( 
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
select id, serial, version, case when rn=1 then 0 else 1 end as is_deleted 
from windows
;


Update based on the above CTE:

with windows as ( 
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
update dbase234936 set is_deleted = case when rn=1 then 0 else 1 end
from windows
where dbase234936.id = windows.id;


I like windowing functions :-)

DB Fiddle link