CODEWITHSUNDEEP
sql-server
Iballan
Iballan

Reputation: 23

Select most recent transactions pr item

On a Microsoft SQL database, I am looking for at way to find the most recent salesdate and customer pr item. If two customers has bought the same item, i just want one of the customers.

Every item should only apear once in my query.

Here is an example of my data:

Rownumber   Item    Customer        Date        QTY
1           10      2000            01/01/2014  5   
2           10      3000            02/01/2014  6
3           10      2000            02/01/2014  4   
4           20      4000            10/01/2014  1
5           20      4000            01/01/2014  8   
6           30      5000            08/01/2014  5
7           40      2000            07/01/2014  10

My result should be somthing like

2           10      3000            02/01/2014  6
4           20      4000            10/01/2014  1
6           30      5000            08/01/2014  5
7           40      2000            07/01/2014  10

Instead of row 2, it could be row three, because they are on the same date. I just need one of them.

I will guess it is some RANK function, but i cannot make it work.

Upvotes: 0

Views: 66

Answers (1)

M.Ali
M.Ali

Reputation: 69554

Using CTE

;WITH Latest
AS
 (
  SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Date] DESC)
  FROM TABLE
 )
SELECT * FROM Latest
WHERE rn = 1

Using Sub-Query

SELECT *
FROM 
(
  SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Date] DESC)
  FROM TABLE
)Q
WHERE rn = 1

Upvotes: 1

Related Questions