tak snad mi tu niekto pomoze.
mam tabulku s clankami, ktore mozu byt topovane, a su zaradene do kategorii..
ked chcem ziskat 3 top clanky pre danu kategoriu tak spravim jednoducho toto:
Kód: Vybrať všetko
SELECT * FROM posts WHERE category_id = 1 AND top_post = 1 LIMIT 0,3 Cize nieco taketo, ale do jednej query:
Kód: Vybrať všetko
SELECT * FROM posts WHERE category_id = 1 AND top_post = 1 LIMIT 0,3
SELECT * FROM posts WHERE category_id = 2 AND top_post = 1 LIMIT 0,3
SELECT * FROM posts WHERE category_id = 3 AND top_post = 1 LIMIT 0,3
...
SELECT * FROM posts WHERE category_id = 10 AND top_post = 1 LIMIT 0,3
hmm, tak nakoniec som nasiel nejake postupy, a vyslo mi z toho toto:
Kód: Vybrať všetko
SELECT x.*
FROM (SELECT t.*,
CASE
WHEN @category != t.category_id THEN @rownum := 1
ELSE @rownum := @rownum + 1
END AS rank,
@category := t.category_id AS var_category
FROM posts t
JOIN (SELECT @rownum := NULL, @category := '') r
WHERE t.top_post = 1 ORDER BY t.category_id ASC, t.top_post DESC, t.published_at DESC) x
WHERE x.rank <= 3(dolezite je tam aj to zotriedenie podla published_at, to som v prvom prispevku nespominal)
