Link to home
Get AccessLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

How to filter out duplicate records based upon more than one column

I have a table named Customers where each customer is uniquely identified by their social security number.
Each customer may have multiple records in the Customers table. I need to populate a second table "NewCust" with records from the Customers table. But the NewCusts table will only contain the record from the Customers table which has the highest amount for the Purchase amount, based upon the Month and Year

Below is an example of what I am talking about, and you can see that the "NewCust" table only has one record
based upon the month, year, and Max purchase price. Sometimes there are two records in the Customers table with the same SSN,Month, and Year. In that case I always want the record with the Max purchase prices.
Can someone help me write a query for this? I know that I want to do an insert on "NewCust" based upon a select from "Customers". Beyond that I am lost and need help. I have scripted and attached both tables.

Customers

SSN            Month   Year      Purchase
23367899   4            2014       3
23367899   4            2014      16
23367899   2            2014      18
23367899   4            2014      99
99110099   2            2014      77
99110099   2            2014      78
99110099   2            2014      80
99110099   3            2014      80

NewCust  (This is how the table will look after running the query_

SSN      Month   Year      Purchase
23367899   4     2014      99
23367899   2     2014      18
99110099   2     2014      80
99110099   3     2014      80
Customers.txt
NewCust.txt
SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of brgdotnet

ASKER

Thank you. I appreciate the help.