SELECT problem
-
I have a table called ORDERS-
ORDERS has Columns - ItemNumber, OrderNumber, Quantity, DueDate
There are approximately 2200 rows with 325 distinct ItemNumbers. What I would like to be able to do is Select an ItemNumber and the next 3 orders(if available) for that ItemNumber Ordered by DueDate asc. Some of the ItemNumbers may have 25 orders and some will have only 1 so I may not be able to list 3 records.
In a nut shell I need to have the 325 items with the next 3 orders for each item when there are 3 orders.
Any suggestion or help is appreciated
Thanks
Kurt Kracaw
-
Try this and see if it works:
select
a.ItemNumber
a.OrderNumber,
a.Quantity,
a.DueDate
from
dbo.Orders a
where
a.OrderNumber in ( select top 3
b.OrderNumber
from
dbo.Orders b
where
b.ItemNumber = a.ItemNumber
order by
b.DueDate
)
order by
a.ItemNumber,
a.DueDate
-
Awesome - exactly what I needed. I see where I was going wrong the second select is on the order number not the item number.
THX
Kurt Kracaw
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply