Dotaz: MySQL GROUP BY a ORDER BY

[filbar]

V MySQL mám takovouto strukturu jako:

otázka1               otázka2     otázka3
|
otázka1odpověď1                   otázka3odpověď1
|
otázka2odpověď2

Otázky mají atribut top=0 a u odpovědí top=id otázky. SQL tabulka vypadí cca takto

CREATE TABLE posts(id int(5) NOT NULL, top int(5) NOT NULL, title varchar(255), dateadd datetime NOT NULL, ......,PRIMARY KEY(id));

Nevíte někdo jak získám výpis, který by obsahoval toto:

otázka2odpověď2
otázka2
otázka3odpověď1

tj. poslední odpověď, nebo samotnou otázku pokud nemá odpověď ale seřazenou podle data přidání sestupně. Zkoušel jsem něco jako:

SELECT MAX(id), top,
FROM posts
GROUP BY top
ORDER BY dateadd DESC

Ale to vrací nesmyslné výsledky.

Předem děkuji za odpovědi.

Odpovědi

[Kit]

Asi to není úplně správně, ale zdá se, že by to mohlo fungovat:

SELECT posts.title, odp.title
  FROM posts LEFT JOIN posts AS odp ON odp.top=posts.id
  WHERE posts.top=0 GROUP BY posts.id;

[l0gik]

Na todle by se správně měli použít window function, který ale v nejsou. Správně by s měl pro každou otázku udělat subselect limit 1, ale to je strašný (pro malou db ale asi taky v pohodě)

Kitovo řešení s group by IMHO fungovat nebude, mysql u group by u sloupců v group by neuvedenejch vrátí libovolnej řádek (zpravidla první, na kterej narazí)

Zkusil bych něco ve stylu

SELECT * FROM post LEFT JOIN 
(
odpovedi 
INNER JOIN
(SELECT top, MAX(date) date FROM  post WHERE top <> 0 GROUP BY top) grouped
ON (grouped.top = odpovedi.top AND odpovedi.date = grouped.date)
)
ON (odpovedi.top = post.id)
WHERE post.top = 0

Smysl je: leftjoinu to na tabulku, kterou nejprve omezím pouze na poslední odpovědi z otázek.

Má to jen tu chybu, že pokud tam budou dvě odpovědi ze stejnýho času, tak tam pro danou otázku budou řádky dvě. To se dá vyřešit různě: Např že k celýmu výsledku leftjoinu výsledek ještě jednou, s podmínkou že id otázky je stejný, ale id odpovědi větší a vrátim jen to, kde ten leftjoin se nepovede (tocelypodruhe.id is null) - tím vyloučím ty duplicitní odpovědi.

Anebo se to dářešit přechodem na nějakou rozumější db, která umí window functions

[Kit]

Kitovo řešení s group by IMHO fungovat nebude, mysql u group by u sloupců v group by neuvedenejch vrátí libovolnej řádek (zpravidla první, na kterej narazí)

Také jsem se divil, že mi to v SQLite funguje podle zadání. Místo libovolného řádku vrací poslední. V MySQL se mi to zkoušet nechtělo. Napsal jsem to jako nástin možného řešení. Musím přece tazateli nechat prostor pro jeho vlastní aktivitu, ne?

[l0gik]

Ten libovolný řádek samozřejmě není libovolný (ve smyslu fce random), ale řádek získaný dle nějakého nedokumentovaného a nezaručeného kritéria: MySQL vrací první na který narazí, SQLite tedy pravděpodobně poslední, na který narazí. - Takže pokud Ti někdy vznikne v db "díra", tak už to nemusí fungovat ni SQLite, protože pořadí fyzického uložení se bude od chronologického lišit.

[Kit]

V SQLite jsou výstupní data vždy seřazena podle ID (pokud je nenecháš seřadit jinak), takže při běžném insertu s autoincrementem se ta díra nikdy nezaplní.

Ale jinak s tebou souhlasím, mé řešení fungovat nemusí, protože neodpovídá specifikaci SQL a využívá vlastnost, která je dána implementací a není garantována.