[SQL server] TOP 10 van subquery

[Steven]

Goededag,

Ik heb een aantal producten, en die hebben allemaal 1 of meerdere productVersions. Nou wil ik een query waarmee ik alle productVersions en hun product eruit haal, echter gelimit op het aantal producten. Heel erg versimpeld komt dat neer op:

SQL:

SELECT
    p.id AS productID,
    pv.id AS pvID
FROM 
    productVersions as pv,
    (SELECT TOP 10 id FROM products) AS p
        
WHERE pv.productID = p.id
ORDER BY p.id

Je zou verwachten dat dit 10 productID's zou retourneren en bijbehorende productVersions id. Alleen dat doet hij niet. Hij komt terug met 117 (opzich kloppende) rijen, verdeeld over 5 (??) producten en wel met kloppende productVersions erbij.

Helemaal raar is dat dat codes zijn van 14737 tot 14742, terwijl als ik los de subquery doe dan krijg ik die van 1000 - 1009. Iemand enig idee welke denkfout ik maak?

[pedorus]

Waarom zou een join tussen 10 records en een andere tabel/query altijd maar 10 resultaten moeten retourneren? Verder doe je geen order by in de subquery, dus van welke 10 producten je alle versies krijgt is ongedefinieerd. Wat had je dan verwacht? Of bedoelde iets als

SQL:

SELECT TOP 10
    p.id AS productID,
    MAX (pv.id) AS pvID
FROM 
    productVersions as pv,
    products as p
WHERE pv.productID = p.id
GROUP BY p.id
ORDER BY p.id

[whoami]

Veel meer dan gissen kunnen we op dit moment niet , denk ik:

Een aantal dingen waar je rekening moet mee houden:
- je haalt wel 10 producten op, maar ben je zeker dat ieder opgehaald product één (of meerdere) productVersions heeft ? Je doet nl. een inner join, wat dus wil zeggen dat de producten die geen productVersion hebben, niet in de resultset worden opgenomen
- Dat je 117 rijen terugkrijgt kan logisch zijn, je haalt nl. voor ieder product alle productVersions op. Dus krijg je een resultset die groter is dan het aantal producten dat je ophaalt.
- Ivm de producten die je terugkrijgt: je doet een SELECT TOP 10, dus de producten die je terugkrijgt zullen de eerste 10 producten zijn, die bepaald worden volgens de volgorde van je clustered index.

[RobIII]

Waar hoort mijn topic?
SEA >> PRG

[Nic]

Microsoft SQL Server?
Dan kun je wat met de RANK() functie

Voorbeeldje:
http://riteshshah.wordpre...lause-in-sql-server-2005/

Het is even puzzelen als je 'm voor de eerste keer gebruikt, maar het werkt wel heel handig.

[_js_]

Je geeft in ieder geval niet aan welke top 10 je wilt hebben (beste, grootste, oudste, etc). Dat is een fout die je er uit kunt halen. Voor die andere fout zul je misschien moeten kijken of er wel productversions zijn voor de producten die je ziet.

[Steven]

@Robill: excuus, was ervan overtuigd dat ik hem in PRG gezet had.

Wel grappig om te zien dat je heel duidelijk denkt te kunnen zijn, maar dan toch helemaal de plank mis kan slaan. Betere poging:

Ik wil producten weer geven, en bij elk product zijn productversies. Ik verwacht dus daadwerkelijk meer dan 10 productversies (oftewel: rijen), maar wel precies 10 verschillen productID's. Overigens heeft idd elk product minstens 1 productVersion.

Een ORDER BY in de subquery zette lost idd het probleem op, maar waarom is me onduidelijk. Het maakt toch niet uit of je 10 willekeurige rijen pakt (wat het zonder ORDER BY toch is?) of gesorteerde rijen?

[pedorus]

Steven schreef op maandag 22 maart 2010 @ 10:21:
Een ORDER BY in de subquery zette lost idd het probleem op, maar waarom is me onduidelijk. Het maakt toch niet uit of je 10 willekeurige rijen pakt (wat het zonder ORDER BY toch is?) of gesorteerde rijen?

Eerst komt de order by, dan de top. Je sorteert dus eerst alle hits, en pakt dan de 10 bovenste. Omdat het om een subquery/view gaat, staan die 10 rijen zelf (theoretisch) niet perse meer op volgorde, maar weet je wel dat het om de 10 bovenste gaat als je het op de gehele set ziet. Een 'order by' in een subquery zonder 'top' is dan ook iets dat sql server weg zal optimaliseren (en een hack daartegen die soms wordt gebruikt is select top 100 percent of select top 99.9999999999999 percent, maar dat is dus een enorme hack die niet gegarandeerd blijft werken).