TOP command (Last 6 Orders) ?

  • Currently, the following code gets the

    "last six orders shipped" shipped for one

    customer.  How can I loop or modify this to get

    the "last six orders shipped" per customer ?

    SELECT     TOP 6 customers.custno, [order-header].entrydate

    FROM         customers INNER JOIN [order-header]

                 ON customers.custedp = [order-header].custedp

    GROUP BY customers.custno,

                [order-header].entrydate, [order-header].orderno

    ORDER BY customers.custno, [order-header].entrydate DESC

     

  • Quick and dirty way is to loop through a distinct set of customer records and bring into a cursor which you can use in a where clause to bring back a normal top 6 statement.

    SELECT     TOP 6 customers.custno, [order-header].entrydate

    FROM         customers INNER JOIN [order-header]

                 ON customers.custedp = [order-header].custedp

                 WHERE CUSTOMERS.UNIQUIEID = cursorCUSTOMERSUNIQUEID

    GROUP BY customers.custno,

                [order-header].entrydate, [order-header].orderno

    ORDER BY customers.custno, [order-header].entrydate DESC

     

    I'm sure someone out there can come up with something dynamic, but if you want it asap this method will work.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Assuming orderno is unique

    SELECT c.custno, h.entrydate, h.orderno 

    FROM customers c

    INNER JOIN [order-header] h

    ON h.custedp = c.custedp

    WHERE h.orderno IN (SELECT TOP 6 h2.orderno

    FROM [order-header] h2

    WHERE h2.custedp = c.custedp

    ORDER BY h2.entrydate DESC)

    ORDER BY c.custno, h.entrydate DESC

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply