Remove duplicate records but keep the oldest record-CodePudding

I'm using Microsoft Access and would like to use a SQL query to remove duplicate records. Where there is a duplicate record, I want to keep the oldest transaction ID (or the lower number). In this scenario, I want to remove John Smith's record with Customer ID "1283."

| CustID | TransID | FirstName | LastName | Date | Email |
|2523 | 0029 | John | Smith | 22/04/19| [email protected]
|2523 | 1283 | John | Smith | 14/07/21 | [email protected]
|3746 | 2306 | Harry | Potter | 29/01/22 | [email protected]

CodePudding user response：

This works in your scenario, but if the customer changes their email you will have two records.

select customerID, min(TransactionID),  FirstName , LastName , min(Date) , Email 
from table
group by customerID, FirstName, LastName, Email

CodePudding user response：

Try using Common Table Expressions (CTE).

check this link