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