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
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
It seems the following line is bad data output:

23367899   2     2014      18


Lets run this first to see if you are getting the expected result:
Select  SSN
     , [Month]
     , [Year]
     , Purchase 
From (Select 
     Row_number() Over(Partition by [Year], [Month] Order By Purchase Desc) As rn
     , SSN
     , [Month]
     , [Year]
     , Purchase
 From Customers) As D
 Where rn =1

Open in new window


Then you can try:
;With cte
As
(
Select 
     Row_number() Over(Partition by [Year], [Month] Order By Purchase Desc) As rn
     , SSN
     , [Month]
     , [Year]
     , Purchase
From Customers
) 
Insert Into NewCust (SSN, [Month], [Year], Purchase)
Select cte.SSN, cte.[Month], cte.[Year], cte.Purchase
From cte
Where rn =1

Open in new window


You may like: Subqueries at a glance
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use a GROUP BY clause:
INSERT INTO NewCust (SSN, Month, Year, Purchase)
SELECT SSN, MONTH, YEAR, MAX(Purchase)
FROM Customers
GROUP BY SSN, MONTH, YEAR

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Thank you. I appreciate the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.