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

 

[a7n9]

Thanks again, Ian. Yes, you are quite close it is for maximum applications by a student and it is going to be the recent one.

And about creating a new table, I cannot help it that's what I've been asked to do to create a new table.

No, not a single field is indexed. and I cannot do it manually cause there will be many tables like this one. and this task has to be done on a daily/weekly basis.

Right now I've over 8000 records and more than 120 fields and it's gonna grow, not the fields but the records.

The approach I'm taking now, you might find it funny. But it is working fast and I understand what's going on.
1. Create a copy of the original table in a tmptbl1
2. Copy all the duplicates in another tmptbl2
3. Keep only the maximum application records in tmptbl2
4. Delete all the duplicates from the first tmptbl1
5. Append the records got from action 3 into tmptbl1

Also, spaces in the fields are coming from other department, so cannot help it too.

If you have any other suggestion please let me know. Thanks for your help.