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

Question

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?

Answer 1

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

Answer 2

This is the unbiquitous top(n) per group, I believe sqlanywhere supports window functions (if you could have ties with multiple Suppliers use dense_rank):

select ArticleId, SupplierId, DeliveryTime
from (
  select *, 
  Row_Number() over(partition by ArticleId order by DeliveryTime) rn
  from Procurement
)t
where rn = 1;

Answer 3

Disable ONLY_FULL_GROUP_BY and add it in the select. Tested on mysql.

SELECT ArticleID, SupplierID, Min(DeliveryTime) AS DeliveryTime FROM mytable GROUP BY ArticleID