Difficult Query

[ChrisM]

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.

 

[ChrisM]

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?