Solved

Last 2 orders by customer

Posted on 2016-09-12
2
52 Views
Last Modified: 2016-09-12
I need to select the last 2 orders and dates by customer from a sales order table.

The table would have the customer number, docdate, order amount, order number etc..

Not quite sure the easiest way to do that.

Thanks
0
Comment
Question by:jdr0606
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41794159
Couple of different ways to do this, but simplest is to use the RANK() keyword to rank all orders for each customer by the order date, then pick the top two.

Table and column names are samples, change these names to fit your situation.
SELECT CustomerID, OrderID, RankOrder
FROM (
   SELECT CustomerID, OrderID, 
     RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) as RankOrder
   FROM SalesOrderTable) a
WHERE RankOrder <= 2

Open in new window

0
 

Author Closing Comment

by:jdr0606
ID: 41794535
Perfect
Thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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