CODEWITHSUNDEEP
sql-servert-sql
Dick Smith
Dick Smith

Reputation: 200

T-SQL max sales date and price on that sales

I have a sales table and products table. I'd like to retrieve the last sales and the product price of the last sales. Is there a query that can do this in a simple way:

Select Max(s.SalesDate), p.ProductName, Max(s.Price)
From Sales s 
inner join products p
group by p.ProductName

This doesn't work because max(price) is not from the last sale

Upvotes: 0

Views: 486

Answers (1)

paparazzo
paparazzo

Reputation: 45096

select * from 
(
Select s.SalesDat, p.ProductName, Maxs.Price
     , row_number() over (partition by p.ProductName order  by s.SalesDat desc) as rn
From Sales s 
inner join products p
) tt 
where tt.rn = 1

clearly you are missing a join condition

Upvotes: 1

Related Questions