[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select Last Sale Date

Posted on 2011-03-14
3
Medium Priority
?
489 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
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 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