sqlsqlanywhere

How do I get the articleID and supplierID with the shortest delivery time?

I have a SQL table called 'Procurement':

ArticleID SupplierID DeliveryTime
1 1 4
1 2 6
1 3 8
2 4 3
2 5 5
2 6 7

I would like to have the supplier with the shortest delivery time for each article:

ArticleID SupplierID DeliveryTime
1 1 4
2 4 3

I have problems getting the correct SupplierID:

SELECT ArticleID, Min(DeliveryTime) AS DeliveryTime 
FROM Procurement
GROUP BY ArticleID

Because of the Group By - clause, the SupplierID column must appear in an aggregation function...

How can I achieve my goal?

Solution

  • You can join your query as a subquery with the table to get the correct SupplierID :

    SELECT p.*
FROM Procurement p
INNER JOIN (
  SELECT ArticleID, Min(DeliveryTime) AS DeliveryTime 
  FROM Procurement
  GROUP BY ArticleID
) AS s ON s.ArticleID = p.ArticleID and s.DeliveryTime = p.DeliveryTime

    Demo here