Duplicate values

[Nuno Gomes]

Hello, all


I have a table that have +/- 40000 records of duplicate entries.

I know i need the record more recent of the duplicates (i have the field
DATA_V to order the records).
My question is how i delete all duplicates less the more recent record?
Or any other way to solve this problem?...


There is the exemple:
My table: PVENDAS
MATRICULA, MARCA, ...., DATA_V, ...
01-05-DF VOLVO 20050105
01-05-DF VOLVO 20050915
01-05-DF VOLVO 20060728
01-05-DF VOLVO 20080312
55-US-01 OPEL 20060502
55-US-01 OPEL 20071108
55-US-01 OPEL 20080705
....

I like to have, in final, my table: PVENDAS
MATRICULA, MARCA, ...., DATA_V, ...
01-05-DF VOLVO 20080312

55-US-01 OPEL 20080705
....




Thank you for all your help.

Nuno Gomes

 

[Allen Browne]

Backup first!

DELETE FROM PVENDAS
WHERE EXISTS
(SELECT MATRICULA
FROM PVENDAS AS T
WHERE T.MATRICULA = PVENDAS.MATRICULA
AND T.MARCA = PVENDAS.MARCA
AND T.DATA_V > PVENDAS.DATA_V);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Michel Walsh]

40K records? Maybe standard joins will be slow, so I will suggest you make a
temporary table, with the three fields, BUT, add an index built on the two
first fields NOT allowing duplicated value. (To define such an index, in the
index form of the table design, supply a name for the index in the first
line and LEAVE that column empty in the second line, second line getting the
second field making that 'compound' index... be sure to check the option
that the index should not allow dup ).


Next, append the data from your initial table to this temp table with an
ORDER BY clause. Jet respects that order by clause (but that is not
documented, so it may be considered as susceptible to change in the future).
The ORDER BY clause must be by DECREASING value of the third field:


INSERT INTO temp
SELECT f1, f2, f3
FROM originalTable
ORDER BY f3 DESC



Execution of that query from the user interface will prompt you that some
records have not been appended due to uniqueness constraint violation...
that is what we want.


The result is then in the temp table.


Remember to define the no dup index on couple { f1, f2}, else, all records
will be appended.

Remember to use the ORDER BY f3 DESC, so the maximum value of f3, for each
{ f1, f2} will be the first one to 'make it' to the table and the smaller
value of f3 will be rejected, for the same { f1, f2} .


Vanderghast, Access MVP