vbnetcoder
asked on
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?
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?
Nakul Vachhrajani
Can you please provide some sample data and expected output to help understand your issue in a better way?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.O
INNER JOIN LastOrders L ON c.LastName = L.LastName
Vaibhav
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.O
INNER JOIN LastOrders L ON c.LastName = L.LastName
Vaibhav
As Nakul Vachhrajani suggested, sample data and expected output would greatly help the experts help you.
ASKER
ty