Select Last Sale Date


I have a SaleTable with upc, movement date, store, vendor and qnty as part of the record.  I have InvTable with  upc, store, and vendor as part of the record.  I need to return the top n records (last n times the upc was sold by date) from the SaleTable for each record in the InvTable.  The returned number of records needs to be for each upc, store, vendor.  So if I want to return the top 2 reocrds from the SaleTable it would be the lastest two dates that the upc was sold for each store and vendor.

Who is Participating?
SharathConnect With a Mentor Data EngineerCommented:
try this.
  FROM (SELECT s.*, 
                 OVER(PARTITION BY s.upc,s.vendor, ORDER BY s.movement_date DESC) rn 
          FROM InvTable i 
               JOIN SalesTable s 
                 ON i.upc = s.upc 
                    AND i.vendor = s.vendor 
                    AND = t1 
 WHERE rn <= 2

Open in new window

Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Since you are using SS2K8, you can use Row_Number to limit your data:
JohnJMAAuthor Commented:
Thanks to both of you.  Sharath your solution was right on the mark.  Shaun Kline thanks for the link
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.