CODEWITHSUNDEEP
sqlsql-servert-sql
hellogoodnight
hellogoodnight

Reputation: 2139

Find all rows with unique column, only the latest

Edit: Purchases contains a column called Date too, of course. My bad.

I have a table, Purchases, with columns CustomerId, PhoneNumber, Purchase, Price and Date.

I want to move information about customers to a new table, Customers. That contains CustomerId and PhoneNumber.

Rows with the same CustomerId can occurr multiple times in Purchases, and I only want the most recent row for each CustomerId, since that will contain the most recent PhoneNumber for that customer.

I read some stuff about DISTINCT but from what I understood that is used to find unique rows (right?).

I have tried some stuff with inner joins but since I am a beginner, I don´t feel like it would contribute much if I posted it :)

Upvotes: 2

Views: 82

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Try something like this:

;WITH MyCustomerRows As
(
    SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY [Date] DESC) AS CustomerIndex
          ,CustomerID
          ,PhoneNumber
    FROM Purchases
)
INSERT INTO NewCustomerTable(CustomerID,PhoneNumber)
SELECT CustomerID,PhoneNumber
FROM MyCustomerRows 
WHERE CustomerIndex =1 --this will pick just the first row, which is the newest...

Upvotes: 5

Related Questions