[Mysql] rank meegeven aan records binnen dezelfde group

[.GoO]

Ik heb een tabel met daarin producten die bij een shop horen. Ik gebruik een query om actuele producten (met huidige datum tussen begin- en einddatum) op te halen, dat kunnen er meerdere per shop zijn. Nu wil ik per shop maximaal 3 producten terugkrijgen, terwijl dat er meer kunnen zijn.

Dit probeer ik op te lossen door producten van dezelfde shop een rank mee te geven, zodat ik dmv een WHERE-clausule producten kan uitfilteren op basis van die rank. Zie onderstaande query:

code:

SET @currcount = NULL, @currvalue = NULL;

SELECT 
    s.id as shopid, s.name as shopname, p.inserted, p.id as productid, p.startdeal, p.enddeal
    ,@currcount := IF(@currvalue = s.id, @currcount + 1, 1) AS rank
    ,' ' + @currvalue AS lastvalue
    ,@currvalue := s.id as currentvalue
FROM 
    product p 
JOIN 
    shop s ON p.shopid = s.id 
WHERE 
    s.status = 'Active' AND 
    s.showfrontpage = 1 AND 
    (now() BETWEEN p.startdeal and p.enddeal) 
ORDER BY s.id, p.inserted DESC

Volgens mij gaat dit in theorie werken, maar in praktijk werkt het deels. Soms nummert hij dezelfde shop netjes op qua rank, maar soms blijft de rank ook op 1 staan. Volgens mij komt dat doordat hij (ondanks mijn order by) dezelfde shop niet achter elkaar verwerkt. In het veld lastvalue zie ik dat de vorige shopvalue niet de huidige shopvalue is (waardoor de rank op 1 blijft staan), terwijl de betreffende records van dezelfde shop zijn en in het resultaat direct onder elkaar staan.

Kan het zijn dat hij de records ondanks de order by niet in de juiste volgorde doorloopt? Heeft iemand een idee hoe dit op te lossen?

[The Eagle]

Zou best kunnen. Ligt er aan hoe de DB engine exact werkt tav query executie; gaat ie eerst de resultset maken en dan orderen, of order ie de tussenresultaten? Geen idee hoe dat bij mysql gaat (Oracle wel, die doet de order by pas op het einde). Bovendien wil je eigenlijk bij elk nieuw ide je values weer terug op 0 stellen en dat vergeet je (wordt in SQL ook lastig).
Standaard voor dit soort dingen is een combi van group by en count gebruikt. Als de ID's alfanumerieke waarden zijn moet je ze volgens mij gewoon kunenn optellen. En dan daarop een order by / group by.

[pedorus]

Als p.inserted uniek is, dan zou je een left self join kunnen doen op eerdere en daarna HAVING count(p2.*) < x. Als in:

SQL:

product p LEFT JOIN product p2 on p.shopid = p2.shopid AND p2.inserted < p.inserted AND 
 [all other where requirements on p2 here]

Is dus helaas niet erg makkelijk of erg snel. Als p.inserted niet uniek is, dan is het alleen maar mogelijk met volgens de documentatie ongedefinieerde resultaten. Dan kun je het waarschijnlijk beter in (niet-sql) code oplossen.