What I want is fairly simpel, but I can't figure it out.
Problem: Select the article (the articles) with the highest price for
each article group.
table articles
---------------
id=1, description='article1', price=100, articlegroupid=4
id=2, description='article2', price=200, articlegroupid=5
id=3, description='article3', price=900, articlegroupid=5
id=4, description='article4', price=900, articlegroupid=5
id=5, description='article5', price=200, articlegroupid=4
id=6, description='article6', price=100, articlegroupid=6
id=7, description='article7', price=300, articlegroupid=6
id=8, description='article8', price=500, articlegroupid=6
id=9, description='article9', price=400, articlegroupid=6
expected result
-------------------
id=5, description='article5', price=200, articlegroupid=4
id=3, description='article3', price=900, articlegroupid=5
id=4, description='article4', price=900, articlegroupid=5
id=8, description='article8', price=500, articlegroupid=6
What I have got is the following (but it doesn't do the trick):
SELECT
artikelen.id,
artikelen.omschrijving,
MAX(artikelen.prijs),
artikelen.artikelgroep
FROM
artikelen
GROUP BY
artikelgroepid
ORDER BY
artikelgroepid
Can you help me out? Thanx
