1

Topic: Removal of counterparts in the table

All greetings.
Help  with the decision of the following task.
There is a table doc, in it of a field stat, nomer. The field stat can accept two values, and a field nomer contains accordingly numbers (which can and repeat). A question: how to delete counterparts of records of a column nomer leaving only last of doubles.
P.S. My request deletes all but the first record of counterparts
--delete from doc
--where nomer in (
--select nomer
--from (select nomer, row_number () over (partition by nomer) as rn from doc) t
--where t.rn> 1)

2

Re: Removal of counterparts in the table

wilwin wrote:

All greetings.
Help  with the decision of the following task.
There is a table doc, in it of a field stat, nomer. The field stat can accept two values, and a field nomer contains accordingly numbers (which can and repeat). A question: how to delete counterparts of records of a column nomer leaving only last of doubles.
P.S. My request deletes all but the first record of counterparts
--delete from doc
--where nomer in (
--select nomer
--from (select nomer, row_number () over (partition by nomer) as rn from doc) t
--where t.rn> 1)

I will formulate your task more distinctly.
In the table there are some lines. for each of two unique values stat there can be some numbers nomer, therefore it is necessary to leave for everyone stat single line with the last value nomer.
Question - as it to make?
The answer - in any way. Because is not present unambiguous the factor which defines "last" of doubles.
For example, lines in sequence.
stat nomer
yes 10
no 11
yes 8
yes 7
no 12
If last in sequence yes-7 and no-12 it is one deal, it is necessary to leave 4th and 5th lines, deleting all remaining. If "last" are maximum then yes-10 and no-12, it is necessary to leave it 1st and 5th lines.
And a problem at you, the author of a subject, that lines from 2 columns lie  and can be returned in any order .
Renumber - and really the request deduces a line so (and  them will number through row_number ()).
That is you decided to leave 4th and 5th, and request with a command delete will work over such dial-up:
yes 8
yes 7
yes 10
no 11
no 12
Also you at all delete that. Clearly, in what a dirty trick?
P.S.  formulate a statement of the problem algorithmically truly and .

3

Re: Removal of counterparts in the table

The table looks so:
stat | nomer |
A 165
A 197
T 165
A 238
T 854
A 197
AND 165
It is necessary to select column doubles nomer, these are records 1 and 3 and 7 (165),
2 and 6 (197) and to delete accordingly 1, 2, 3

4

Re: Removal of counterparts in the table

wilwin wrote:

the Table looks so:
stat | nomer |
A 165
A 197
T 165
A 238
T 854
A 197
AND 165
It is necessary to select column doubles nomer, these are records 1 and 3 and 7 (165),
2 and 6 (197) and to delete accordingly 1, 2, 3

key here "the table looks ".
That at you returned as A 165, and then A 197, in other request returns as A 197, A 165.
Implicit row number only for this request and this sorting order stat | nomer |
1 A 165
2 A 197
3 T 165
4 A 238
5 T 854
6 A 197
7 AND 165
"These are records 1 and 3 and 7 (165)" is now they 1 and 3 and 7 and if will be 3 and 7 and 1, you want to delete first two which now 3 and 7 and to leave 1?
I reformulate. You here found superfluous lines.
Delete request delete from table where stat = "A" and nomer = 165. And the request deletes all three lines, 1, 3 and 7.
And absolutely other deal if there was a unique row number and request would turn in
delete from table where stat = "A" and nomer = 165 and row_number in (1,3). And the line 7 would remain on a place.
Or to you all the same what 2 from 3 lines (1-3-7) to delete? That is it is necessary to clean the table simply?
And you make so - insert into table2 select tt.stat, tt.nomer from (select stat, nomer, row_number () over (partition by stat, nomer order by nomer desc) as row_number_in_tuple) tt where tt.row_number_in_tuple = 1
And  then truncate table, then insert into table () select * from table2 and then drop table2.
How to you such variant?

5

Re: Removal of counterparts in the table

No, what lines to delete not all the same. It is necessary to leave last of "pack" of counterparts.
I added a column  id, now each line has the unique number.
stat | nomer | id
A | 569 | 1
A | 765 | 2
T | 138 | 3
A | 569 | 4
T | 366 | 5
A | 138 | 6
T | 555 | 7
T | 569 | 8
Only how to specify deleted values for each pack of doubles???
SELECT * FROM doc WHERE nomer IN (SELECT nomer FROM doc GROUP BY nomer HAVING Count (*)> 1) ORDER BY nomer, id desc

6

Re: Removal of counterparts in the table

ctid

7

Re: Removal of counterparts in the table

Here wrote request:
delete from doc
where nomer
(
select nomer from (select id, stat, nomer, row_number () over (partition by nomer order by id desc) as rnum from doc) t
where t.rnum> 1
)
But it all the same deletes all counterparts,

8

Re: Removal of counterparts in the table

wilwin wrote:

the Table looks so:
[CSV = |] stat|nomer|ID
A|165|1
A|197|2
T|165|3
A|238|4
T|854|5
A|197|6
|165|7
It is necessary to select column doubles nomer, these are records 1 and 3 and 7 (165),
2 and 6 (197) and to delete accordingly 1, 2, 3

wilwin wrote:

Here wrote request:
delete from doc
where nomer
(
select nomer from (select id, stat, nomer, row_number () over (partition by nomer order by id desc) as rnum from doc) t
where t.rnum> 1
)
But it all the same deletes all counterparts,

Here so try:

delete from doc
where id not in (select max (id) from doc group by nomer);

For check: the link

9

Re: Removal of counterparts in the table

Yes, THANKS Anna that is necessary.