Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select Last Sale Date

Posted on 2011-03-14
3
Medium Priority
?
484 Views
Last Modified: 2012-08-13
Hello,

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.

Thanks,
John
0
Comment
Question by:JohnJMA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 200 total points
ID: 35131340
Since you are using SS2K8, you can use Row_Number to limit your data:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1800 total points
ID: 35131673
try this.
SELECT * 
  FROM (SELECT s.*, 
               ROW_NUMBER() 
                 OVER(PARTITION BY s.upc,s.vendor,s.store ORDER BY s.movement_date DESC) rn 
          FROM InvTable i 
               JOIN SalesTable s 
                 ON i.upc = s.upc 
                    AND i.vendor = s.vendor 
                    AND i.store = s.store) t1 
 WHERE rn <= 2

Open in new window

0
 

Author Closing Comment

by:JohnJMA
ID: 35141871
Thanks to both of you.  Sharath your solution was right on the mark.  Shaun Kline thanks for the link
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question