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