figure out the last order

I have to write a query like this

SELECT LastName, Order ID, OrderDate from Table.

I need to return all the records somebody placed and order of '001' BUT where it wasn't there last item they ordered.

How would I update this query to do that?
vbnetcoderAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Well, guessing that OrderID is meant to be the Item '001' but not sure, so included a column for '001' named ItemCode.

You havent told us what version of SQL Server, so assuming fairly recent ?

Have you used or know about CTE (Common Table Expression) ? It is a bit like doing a subquery first, then we can refer to it in the rest of our query...

If I understand the problem properly, we need to find orders where '001' has been ordered, but isnt the last one.

So, just to get the ball rolling... What happens if you were to try :

; with Order_CTE as
(  SELECT LastName, OrderID, ItemCode, OrderDate, row_number() over (partition by LastName Order by LastName, Orderdate Desc) as RN 
   FROM Table
)
 Select * from Order_CTE C1
 where C1. ItemCode = '001'
 And C1.ItemCode <> (Select top 1 ItemCode from Order_CTE C2 where C1.LastName = C2.LastName and C2.RN = 1) 

Open in new window


Probably better ways to do it, but as I said, it is a starting point while waiting for clarification

Cheers,
Mark Wills
1
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you please provide some sample data and expected output to help understand your issue in a better way?
1
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello vbnvetcoder

Please use below solution. With this solution you will get all the orders for any Last Name where the Last Order is not '001' although can be present at other places.

;WITH cteorders AS
(
      SELECT * FROM
      (
            SELECT u.LastName, u.[OrderID], u.[OrderDate]
            FROM
            (
                  SELECT DISTINCT LastName FROM LastOrders
            )x
            CROSS APPLY
            (
                  SELECT TOP 1 *
                  FROM LastOrders k
                  WHERE k.LastName = x.LastName                  
                  ORDER BY OrderDate DESC
            )u
      )P WHERE [OrderID]  <> '001'
)
SELECT L.LastName,L.OrderDate,L.OrderId FROM cteorders c
INNER JOIN LastOrders L ON c.LastName = L.LastName

Vaibhav
0
 
awking00Commented:
As Nakul Vachhrajani suggested, sample data and expected output would greatly help the experts help you.
0
 
vbnetcoderAuthor Commented:
ty
0
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.