SQL Sever - Group last paid price by customer and product code

I am trying to write a query that will do the following.

I have a table with seperate sales order lines on it. Each line details the customer, product sold, the price sold at, and the date of the sale.

I am trying to establish for each product code, what the last price we sold it for was for each separate customer.

For example using my input below I would expect Product code ABC to return '10' for Brian, '20' for Gary, and '50 for Sam.

Below is complete set of results I would expect for all product codes.
Who is Participating?
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You have many ways to do this. Here's one:
WITH CTE_LastCustSaleByProd
AS (
      SELECT Customer, Product, MAX(Date) LastDate
      FROM Sales
      GROUP BY Customer, Product
SELECT s.OrderNo, s.Customer, s.Product, s.Date, s.Price
FROM Sales s
    INNER JOIN CTE_LastCustSaleByProd c ON s.Customer = c.Customer AND s.Product = c.Product AND s.Date = LastDate

Open in new window

Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT qry1.[Product Code], qry1.Customer, qry1.Price, qry1.Date
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Product Code], Customer ORDER BY Date DESC) AS row_num
    FROM dbo.table_name
) AS qry1
WHERE row_num = 1
ORDER BY qry1.[Product Code], qry1.Customer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.