Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Difficult Query

Discussion in 'Information Technology' started by ChrisM, Jul 28, 2006.

  1. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Hi,

    I have a table that looks a bit like:
    PART_NUMBER
    SUPPLIER_NAME
    DELIVERY_DATE

    A part can have any number of suppliers. What I want to do is write a query
    that will show that last 3 DIFFERENT suppliers (according to delivery_date)
    for a given part number.

    so:
    PART SUPP DELIVERY
    A A June 1
    A A June 2
    A B June 3
    A B June 4
    A C June 5
    A D June 6
    A D June 7
    A D June 8

    Should give me:
    SUPP
    D
    C
    B

    Anyone care to give me a hand?

    Thanks,

    Chris.
     
  2. Loading...


  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:9LydnaFkCNLjCjbZRVnyuw@bt.com...
    > Hi,
    >
    > I have a table that looks a bit like:
    > PART_NUMBER
    > SUPPLIER_NAME
    > DELIVERY_DATE
    >
    > A part can have any number of suppliers. What I want to do is write a
    > query that will show that last 3 DIFFERENT suppliers (according to
    > delivery_date) for a given part number.
    >
    > so:
    > PART SUPP DELIVERY
    > A A June 1
    > A A June 2
    > A B June 3
    > A B June 4
    > A C June 5
    > A D June 6
    > A D June 7
    > A D June 8
    >
    > Should give me:
    > SUPP
    > D
    > C
    > B
    >
    > Anyone care to give me a hand?
    >
    > Thanks,
    >
    > Chris.
    >


    OK, having had a bit more of a hard think... this seems to work nicely:

    SELECT SUPPLIER, Max(BATCH_DATE) AS LastOrder, Count(BATCH_DATE) AS
    TotalOrdersFromThisSupplier
    FROM MyPartsTable
    WHERE (((PART_NO)=[pPartNo]))
    GROUP BY SUPPLIER;

    Nothing wrong with that approach is there?
     

Share This Page