Select Top 5 per debiteur

[Nico84]

Met de volgend query krijg ik de hoogste 5 factuurnummers.

SELECT TOP 5 tbl_Debiteuren.DebiteurID, tbl_Debiteuren.Debiteurnaam, tbl_Factuur.Factuurnummer, tbl_Factuur.Factuurdatum, tbl_Factuur.BedragExcl
FROM tbl_Debiteuren INNER JOIN tbl_Factuur ON tbl_Debiteuren.DebiteurID = tbl_Factuur.DebiteurID
ORDER BY tbl_Factuur.Factuurnummer DESC;

Is het mogelijk om met Select de 5 hoogste (laatste) facturen te tonen per debiteur?
Dus klant A de 5 hoogste factuur nummers, Klant B de 5 hoogste factuur nummers enz enz.

 

[OctaFish]

Daar heb je een subquery voor nodig. Zonder de tabellen is het een beetje lastig, maar dit zou 'm moeten zijn:

SELECT tbl_Debiteuren.DebiteurID, tbl_Debiteuren.Debiteurnaam, tbl_Factuur.Factuurnummer, tbl_Factuur.Factuurdatum, tbl_Factuur.BedragExcl
FROM tbl_Debiteuren INNER JOIN tbl_Factuur ON tbl_Debiteuren.DebiteurID = tbl_Factuur.DebiteurID
WHERE (
   tbl_Factuur.Factuurnummer In 
   (SELECT TOP 5 tbl_Factuur.Factuurnummer FROM tbl_Factuur AS Dupe WHERE Dupe.DebiteurID = tbl_Factuur.DebiteurID ORDER BY Dupe.Factuurnummer DESC)
)
ORDER BY tbl_Debiteuren.Debiteurnaam, tbl_Factuur.Factuurnummer DESC;

De code is gebaseerd op een query die prima werkt op één tabel (Orders in Northwind). En die ziet er zo uit:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE (
   Orders.OrderID In 
   (SELECT TOP 3 OrderID FROM Orders AS Dupe WHERE Dupe.CustomerID = Orders.CustomerID ORDER BY Dupe.OrderID DESC)
)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

 

[Nico84]

Bedankt OctaFish. Dit werkt uitstekend.

Alleen die vergelijking in die subquery komt niet helemaal bij me binnen.

Had zelf bedacht om in vba een lus te maken die voor iedere debiteurid de select top5 uitvoerd en
vervolgens een toevoegquery die de uitkomst in een tijdelijke tabel plaats.

Maar dit is toch wel een stuk handiger

 

[OctaFish]

Subqueries zij reuze handig om een preselectie te maken. En dat is dus precies wat hier gebeurt. Om het voor iedereen die het draadje leest helder te houden, leg ik het principe uit a.d.h.v. het tweede voorbeeld, omdat daar maar één tabel in gebruikt wordt. Al werkt het dus ook bij meerdere tabellen, zoals je hebt gezien

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID FROM Orders
WHERE
   Orders.OrderID In (

Tot hiertoe een normale query; je maakt een selectie met een filter op basis van het veld OrderID. Je wilt namelijk de orders filteren.

   (SELECT TOP 3 OrderID FROM Orders AS Dupe WHERE Dupe.CustomerID = Orders.CustomerID ORDER BY Dupe.OrderID DESC)
)

De subquery is een query die een kopie gebruikt van de hoofdtabel. Die wordt Dupe genoemd. Een query op basis van twee tabellen zonder criterium noemen we een cartesisch product, en bevat alle combinaties van alle records uit beide tabellen. Simpel voorgesteld: als in tabel 1 10 records zitten, en in tabel 2 25, dan wordt eerst een combinatie gemaakt van [tabel 1].[record 1] + [tabel 2].[record 1], [tabel 1].[record 1] + [tabel 2].[record 2], [tabel 1].[record 1] + [tabel 2].[record 3] t/m [tabel 1].[record 1] + [tabel 2].[record 25]. Vervolgens [tabel 1].[record 2] + [tabel 2].[record 1] en zo door. Kortom: een query met 10 * 25 = 250 records. Koppel je het sleutelveld van de twee (nogmaals: identieke) tabellen aan elkaar, dan heb je weer 10 records, want elke sleutel is uiteraard uniek. Da's een beetje zinloos, en doen we dus niet!
Wat wèl zin heeft, is een koppeling met een herhalend gegeven. Bijvoorbeeld CustomerID in Orders. Een klant plaatst namelijk meerdere orders, en komt dus vaker voor in de tabel. In het voorbeeld wordt uit de tabel Orders het veld CustomerID gekoppeld aan het veld CustomerID uit de duplicaat tabel. We hebben dus nog steeds een query met een cartesisch product.
Een cartesisch product met een filtering op een herhalend veld maakt de subtabel dus kleiner. Hoeveel kleiner? Als je niks filtert, zie je alle records van de gekoppelde klanten. Vandaar dat we met TOP 3 het aantal records per klant terug brengen tot 3 records per customerID. Welke records je ziet in de subquery hangt af van de sortering. Je kunt ook de eerste records zien als je oplopend sorteert; dat heeft doorgaans niet zoveel zin, dus sorteer je aflopend om de laatste records te zien.

En dat is eigenlijk de theorie achter de subquery: kies het veld waarvan je de subselectie wilt zien, en maak een criterium dat die voorselectie maakt. Daar rolt een query uit met veldwaarden, en dat is dan de filtering voor je hoofd query. In die query zet je dan wat je nog meer wilt zien.

 

[Nico84]

Bedankt voor de heldere uitleg.

Als je ook nog op jaar wil filteren waar zet je deze criteria dan neer? Dus de top 5 van het jaar 2011.

In de subquery of is het verstandiger om eerst een query te maken die eerst op het jaar filtert en vervolgens de select top 5 query hierop te baseren.

 

[OctaFish]

Dan krijg je zoiets:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE (((Orders.OrderID) In (SELECT TOP 3 OrderID FROM Orders AS Dupe WHERE Dupe.CustomerID = Orders.CustomerID And Year([Orderdate]) = [Typ het jaartal:] ORDER BY Dupe.OrderID DESC)))
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;