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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you please provide some sample data and expected output to help understand your issue in a better way?
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

Mark Wills

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 u.LastName, u.[OrderID], u.[OrderDate]
                  SELECT DISTINCT LastName FROM LastOrders
            CROSS APPLY
                  SELECT TOP 1 *
                  FROM LastOrders k
                  WHERE k.LastName = x.LastName                  
                  ORDER BY OrderDate DESC
      )P WHERE [OrderID]  <> '001'
SELECT L.LastName,L.OrderDate,L.OrderId FROM cteorders c
INNER JOIN LastOrders L ON c.LastName = L.LastName

awking00Information Technology SpecialistCommented:
As Nakul Vachhrajani suggested, sample data and expected output would greatly help the experts help you.
vbnetcoderAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.