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:
1210001
2210001
1210002
1210003
2210002
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?
1210002
1210003
2210002
4210001
7210001
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
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));