get filtered rows by especific rules in postgres12

Question

Aditional info: My table works from 2 lines, first and second line, id always start by 1 or 2, but sometimes, we have to reprocess it, and number get updated

i have a query that shows a lot of id's usually, mi id's start by 1 or 2 for example:

sometimes, this rows are updated for several reason's, when system update it, first number get +2

1210001
2210001
1210002
1210003
2210002
3210001 from 1210001
4210001 from 2210001

same id can be updated from 2 to 3 times

1210001
2210001
1210002
1210003
2210002
3210001
4210001
5210001 from 3210001
7210001 from 5210001

how can I query only last updated of each id?

my table is composed by two working lines, line 1 and line 2

for example, id: 1210001 and 2210001

this id's are for line 1 and line 2.

x21xxxx this is the year and xxx0001 last numbers are consecutive for each line

first number can be odd or even, i am trying to think a query to remove old id's from result

So it's a counter, not an id. Right? And how many digits do you have for this counter? — Frank Heikens, Jan 21 '21 at 10:30
Your example is not clear to me: you said, when updated, the first number increases +2. However, your result adds two records with +1 instead. Is that what you mean? If so, why do you left out the 6210001 in the next example? And finally: If you just want to show the most recent, why are there two records for 0002 and two for 0001? — S-Man, Jan 21 '21 at 10:49
@ChristopheRoussy i already filtered other data from query, inserted inside a "with" but i only have this last step to finish — Dani V, Jan 21 '21 at 11:46
@FrankHeikens if first number is odd, then are diferent 'id's not updated i have 2 working lines, one start by 1 and other start by 2, when number increases +2, they still being diferent — Dani V, Jan 21 '21 at 11:53

mhawke · Accepted Answer · 2021-01-22T01:57:25.577

Check out db<>fiddle example

A bit of maths helps here.

Given that the lower 6 digits of the "id" field are significant for partial grouping, these lower 6 digits can be obtained by "id" mod 1000000.

The upper digits can then be obtained by integer division of the "id" by 1000000. The upper digits define groups based on whether they are odd or even (parity), thus this query will select latest updated "id"s for each line:

select max(id) as last_updated
from t
group by ((id / 1000000) % 2, id % 1000000);

This query groups the rows by the parity of the upper digits (>= 7) and the numeric value of the lower 6 digits. It then selects the largest id of each group.

If you want to delete the older rows:

delete from t
where id not in
    (select max(id) as last_updated
     from t
     group by ((id / 1000000) % 2, id % 1000000));

Check out db<>fiddle example

get filtered rows by especific rules in postgres12

1 Answers1