1

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);
Improve this question
1
  • 2
    The whole point of SQL is that it doesn't matter if you have groups with 3 items or 3000! Could you provide the data in the form of INSERT INTO blah VALUES (...); and also the table structure CREATE TABLE blah (...); - in text, no screenshots please!
    – Vérace
    Apr 16, 2019 at 15:33

2 Answers 2

Reset to default
1

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

Improve this answer
1

To solve this, I did the following (see fiddle):

Table and data

CREATE TABLE my_tab
(
  id INTEGER,
  serial TEXT,
  version TIMESTAMP,
  is_deleted INTEGER
);  

INSERT INTO my_tab VALUES (10, 'AAAAA', '2019-04-14 15:28:08', 0);
INSERT INTO my_tab VALUES (22, 'AAAAA', '2019-03-04 15:28:08', 0);
INSERT INTO my_tab VALUES (13, 'AAAAA', '2019-02-10 15:28:08', 0);
INSERT INTO my_tab VALUES (40, 'BBBBB', '2019-04-17 15:28:08', 0);
INSERT INTO my_tab VALUES (27, 'BBBBB', '2019-02-20 15:28:08', 0);
INSERT INTO my_tab VALUES (17, 'CCCCC', '2019-03-04 15:28:08', 0);
INSERT INTO my_tab VALUES (35, 'CCCCC', '2019-01-01 15:28:08', 0);

SQL:

WITH cte AS
(
  SELECT serial, MAX(version) AS ver 
  FROM my_tab
  GROUP BY serial
),
ids AS
(
  SELECT m.id
  FROM cte c
  JOIN my_tab m
  ON c.serial = m.serial AND c.ver = m.version
)
UPDATE my_tab 
SET is_deleted = 1
WHERE id NOT IN (SELECT id FROM ids);

Then:

SELECT * FROM my_tab;

Resutlt:

id  serial  version is_deleted
10  AAAAA   2019-04-14T15:28:08.000Z    0
22  AAAAA   2019-03-04T15:28:08.000Z    1
13  AAAAA   2019-02-10T15:28:08.000Z    1
40  BBBBB   2019-04-17T15:28:08.000Z    0
27  BBBBB   2019-02-20T15:28:08.000Z    1
17  CCCCC   2019-03-04T15:28:08.000Z    0
35  CCCCC   2019-01-01T15:28:08.000Z    1

Also, a fiddle with the Boolean instead of INTEGER for is_deleted.

You should accept Philᵀᴹ's answer - it's more elegant than mine. I'm working on Window/Analytical functions at the moment!

Improve this answer
2
  • can you please explain what does cte mean?
    – humkins
    Apr 17, 2019 at 7:30
  • 1
    Common Table Expression -AKA the WITH clause. It allows you to make subqueries which can be reused throughout the main query. It really helps tidy up syntax and makes queries much easier to read. The shorthand term cte as I've used it is just a name - I could have used WITH xyz AS... rest of query. I've used 2 CTEs whereas the other poster used 1 (more elegant - because he also used a window function). The only good thing about my solution is that it really highlights that CTEs can be chained leading to relatively simple solutions for what might have been complex problems
    – Vérace
    Apr 17, 2019 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.