0
I have the following data: note that there are some Ids that contain more than 1 record, would like to delete the repeated record whose value is the lowest among the repeated ones the table may have more than 2 repeated records with different values
CDDA ID CPF_1 DATAEMISSAOAPURADA UF NOMEMUNICIPIO VALOR
----------- ------------------------- ------------- ----------------------- ------------ -------------------------------------------------- -------
1693 500809150947 656******** 2015-09-08 00:00:00 MA Sao Raimundo do Doca Bezerra 88476
1711 00029-080915 428******** 2015-09-08 00:00:00 PB Alagoa Grande 57418
1726 400809150949 017******** 2015-09-08 00:00:00 RS Ibiruba 29481
1769 200809150951 571******** 2015-09-08 00:00:00 RO Theobroma 24309
1773 870809150952 323******** 2015-09-08 00:00:00 ES Castelo 36363
1843 200809150957 3920******* 2015-09-08 00:00:00 SC Itaiopolis 44584
1843 200809150957 3920******* 2015-09-08 00:00:00 SC Itaiopolis 33168
1907 350809151001 08735****** 2015-09-08 00:00:00 BA Contendas do Sincora 30266
1907 350809151001 08735****** 2015-09-08 00:00:00 BA Contendas do Sincora 30545
1921 720809151002 2072******* 2015-09-08 00:00:00 AL Pao de Acucar 22730
Ideal result
CDDA ID CPF_1 DATAEMISSAOAPURADA UF NOMEMUNICIPIO VALOR
----------- ------------------------- ------------- ----------------------- ------------ -------------------------------------------------- -------
1693 500809150947 656******** 2015-09-08 00:00:00 MA Sao Raimundo do Doca Bezerra 88476
1711 00029-080915 428******** 2015-09-08 00:00:00 PB Alagoa Grande 57418
1726 400809150949 017******** 2015-09-08 00:00:00 RS Ibiruba 29481
1769 200809150951 571******** 2015-09-08 00:00:00 RO Theobroma 24309
1773 870809150952 323******** 2015-09-08 00:00:00 ES Castelo 36363
1843 200809150957 3920******* 2015-09-08 00:00:00 SC Itaiopolis 44584
1907 350809151001 08735****** 2015-09-08 00:00:00 BA Contendas do Sincora 30545
1921 720809151002 2072******* 2015-09-08 00:00:00 AL Pao de Acucar 22730
In the final result records with the lowest value are deleted, maintaining 1 ID with 1 value.
Have another similar question: How to delete all duplicates except one?
– Sorack
The best way depends on the DBMS because they have different tools , inform the Bank.
– Motta
Perfect, this solves my problem, but the table contains a few million records, pulling it twice ends up being time consuming and ineffective, is there any other way to solve? @Sorack
– unkbr