Delete duplicate records

[a7n9]

Hello all,

I want a SQL code to delete all the duplicate records but one where the record's field2 value is maximum.

For eg:
Field1 Field2
55 33
44 22
222 22
44 11
55 56
55 31

So if I run the query the final table should look like
Field1 Field2
44 22
222 22
55 56

I found SQL code to delete all the duplicate records but not where I can put a condition on the second field. Also, I'm very new at SQL so please help me.

DELETE *
FROM Table1
WHERE (((Table1.Field1) In (SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1] HAVING Count(*)>1 )));

Edit 1: I'm checking the duplicate records for field1

 

[Norie]

I'm sorry but I can't understand the logic of your result.

Is this not what you want?

Field1	Field2
222	22
55	56

I got that result with the following query:

DELETE Table1.Field1, Table1.Field2, *
FROM Table1
WHERE (((Table1.Field1) In (SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1] HAVING Count(*)>1 )) AND ((Table1.Field2)<>DMax("[Field2]","Table1")));

 

[a7n9]

Thanks Norie for your reply. Probably I was not clear stating my problem. The query should delete all the duplicate records for field1 but leave one duplicate record where field2 value is maximum.
As given in my example:
Original Table:
Field1 Field2
55 33
44 22
222 22
44 11
55 56
55 31

Now here we can see that there 3 duplicate records of 55 and 2 duplicate records of 44. Now the query should keep the second last row because field2 value is maximum for the duplicate records of 55. Also, it should keep second row as the field2 value for the duplicate records of 44 is maximum on the second row. And rest duplicate records can be deleted. Therefore the result would be

Field1 Field2
44 22
222 22
55 56

Also, I ran your code but it's deleting all duplicate records. THanks for your help.

 

[Norie]

Don't you just mean you want the maximum for field 2 for each value in field 1?

 

[a7n9]

Yes, that's what I mean and your code runs perfectly fine. My data type specification for field2 was wrong. It was kept as text but when I changed it to integer it runs fine.

Thanks a lot.

 

[a7n9]

Hey Norie,

I was trying your code one more time but when I ran it now it is deleting all the duplicate records. Here's my code. Can you please tell me what is the error?Thanks for your help.

Dim tbl_name_C As String, tbl_name_O As String
tbl_name_O = InputBox("Enter the name of the original table", "Original Table")
tbl_name_C = InputBox("Enter the name of the new duplicates table", "Duplicates Table")

Dim strSQL4 As String
'Create a copy of the original table
strSQL4 = "SELECT [ID Number],[Application Number] INTO " & tbl_name_C & " FROM " & tbl_name_O & ";"
MsgBox strSQL4
DoCmd.RunSQL strSQL4


Dim strSQL As String
'delete duplicates from the table
strSQL = "DELETE " & tbl_name_C & ".[ID Number]," & tbl_name_C & ".[Application Number],*" & _
" FROM " & tbl_name_C & _
" WHERE (((" & tbl_name_C & ".[ID Number]) In (SELECT [ID Number]" & _
" FROM [" & tbl_name_C & "] As Tmp GROUP BY [ID Number] HAVING Count(*)>1 ))" & _
" AND ((" & tbl_name_C & ".[Application Number])<>DMax(" & Chr(34) & "[Application Number]" & Chr(34) & "," & Chr(34) & tbl_name_C & Chr(34) & ")));"
MsgBox "" & strSQL
DoCmd.RunSQL strSQL

 

[a7n9]

Hi again,

I think I've spotted the error but cannot find a solution to it. See what your code doing is finding maximum from all of the records for the second field, but I want the maximum only for the duplicate records. I will explain my example again.

This is the original table
ID NUMBER Application NUmber
22 44
22 45
22 89
40 32
33 90
44 90
44 01

and the result table should be
ID NUMBER Application NUmber
22 89
40 32
33 90
44 90

 

[Ian Mac]

Does the following do what you need?

SELECT testtable.IDNumber, Max(testtable.ApplicationNumber) AS ApplicationNumber
FROM testtable
WHERE (testtable.IDNumber) In (SELECT [IDNumber]
FROM testtable GROUP BY [IDNumber] HAVING Count(*)>1)
GROUP BY testtable.IDNumber;

the following is what I get:

Book1
	A	B	C	D	E
1	TestTableBefore			TestTableAfter
2	IDNumber	ApplicationNumber		IDNumber	ApplicationNumber
3	1	89		12	70
4	7	81		16	90
5	8	17		22	75
6	12	70		33	58
7	12	56		40	37
8	12	44		44	71
9	16	90
10	16	43
11	22	21
12	22	39
13	22	75
14	33	38
15	33	28
16	33	58
17	34	22
18	40	37
19	40	21
20	44	69
21	44	71
22	44	54
Sheet1

I would also recommend you don't use Spaces in your field headings ie will be far better for coding AND easier to read without all those pesky '['s.

Regards,

 

[a7n9]

Thanks Ian. That will do. However, there is a problem. Now I want to get all the records from the original table for the those IDnumbers we got from your query into a new table.
For Ex:

Book1
	A	B	C	D	E	F	G	H	I	J
1	OriginalTable					NewTable
2	IDNumber	ApplicationNumber	Field3	Field4	Field5	IDNumber	ApplicationNumber	Field3	Field4	Field5
3	1	89	d	f	r	12	70	h	h	fd
4	7	81	g	h	h	16	90	fd	h	fd
5	8	17	h	h	h	22	75	f	f	fd
6	12	70	h	h	fd	33	58	Q	K	U
7	12	56	fd	hf	df	40	37	J	L	B
8	12	44	df	h	fd	44	71	Z	N	Z
9	16	90	fd	h	fd
10	16	43	fh	f	h
11	22	21	f	hf	h
12	22	39	f	f	fd
13	22	75	f	f	fd
14	33	38	H	H	M
15	33	28	R	C	C
16	33	58	Q	K	U
17	34	22	F	P	T
18	40	37	J	L	B
19	40	21	P	X	Z
20	44	69	E	E	R
21	44	71	Z	N	Z
22	44	54	V	I	R
Sheet1

Also, tried getting all the fields while checking for the maximum condition but the query runs awfuly slow. Can you please help me with that?

Thanks.

 

[Ian Mac]

Can I ask what the purpose of this is?

Are trying to have a table with the most recent applications in?
Or am I way off the mark.
From what I can, sort of, see, I think you may be going about this the wrong way.

If you intent to repeat this process agian in the future then you shouldn't be creating a Table from a Table, as it's not a good idea to keep the same data in 2 places.

Can expand on what you need so we don't give you advice which may be ill advised.

As for your slow Query, are all for the fields indexed properly?
And how many records is this working with? Fields/Records

Edit Against my better judgment:

SELECT NewTable.IDNumber, NewTable.ApplicationNumber, OldTable.Field3, OldTable.Field4, OldTable.Field5 INTO NewTable2
FROM NewTable LEFT JOIN OldTable ON (NewTable.IDNumber = OldTable.IDNumber) AND (NewTable.ApplicationNumber = OldTable.ApplicationNumber);

Cheers,