SQLTeam.com | Weblogs | Forums

Last price item per customer


#1

Hello everyone,

I want to get the last price paid for each item for each customer.
I have the following columns in table sales.

customer, item, price, rowid

I hope someone can show me the sql query that will give me the results i need.
Thank you !


#2

Let's consider below sample table data

customer item price rowid
a abc 100.00 1
a xyz 200.00 2
a abc 500.00 5
a xyz 700.00 6
b abc 800.00 7
b abc 100.00 3
c abc 100.00 4
c abc 900.00 8

Query will be as below

with cte as
(select customer,item,price,rowid,ROW_NUMBER() over(partition by customer,item order by rowid desc) rnm from test)
select customer,item,price from cte where rnm = 1