Ted James Ted James - 4 days ago 6
SQL Question

Return one SQL row per product with price and latest date

I am not a SQL guy, I have used it in the past and rarely have an issue that cant be solved by google... however this time I need to ask the Community.

I have a database with a table called 'Transactions' it has data like this:

ProdNo | Price | TransactionDate | PurchasedBy | etc.....
----------------------------------------------------------
3STRFLEX | 13.02 | 20162911 | AWC | .....
3STRFLEX | 15.02 | 20162011 | DWC | .....
3STRFLEX | 15.02 | 20160101 | AWC | .....
AFTV2 | 35.49 | 20162708 | AWC | .....
AFTV2 | 29.99 | 20160106 | DWC | .....
AFTV2 | 29.99 | 20160205 | AWC | .....


The desired output is:

ProdNo | Price | TransactionDate
-----------------------------------
3STRFLEX | 13.02 | 20162911
AFTV2 | 35.49 | 20162708


I have tried a to write this myself and I ended up with SQL like this:

select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price
from Transactions t
inner join (
select ProdNo, max(TransactionDate) as 'LastPurchaseDate'
from Transactions
WHERE Price > 0
group by ProdNo
) tm on t.ProdNo = tm.ProdNo and LastPurchaseDate = tm.LastPurchaseDate


However in my data set this returns (cut down) which shows multiple rows per product

ProdNo | LastPurchaseDate | Price
3STRFLX | 20120924 | 0.000000
3STRFLX | 20120924 | 22.000000
3STRFLX | 20150623 | 0.000000
3STRFLX | 20150623 | 1.220000
3STRFLX | 20150623 | 1.222197


So to confirm: I would like 1 row per product for the latest date it was purchased regardless of the price, but I need the price in the returned data.

Thanks

Answer

You can use a CTE and the ranking function PARTITION BY:

WITH CTE AS
(
    select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price,
           rn = row_number() over (partition by ProdNo order by TransactionDate desc)
    from Transactions t
)
SELECT ProdNo, LastPurchaseDate, Price  FROM CTE WHERE RN = 1
Comments