[SQL] parent sorteren op laatste child

[amoen]

hopelijk een duidelijke titel

ik heb 1 tabel:

code:

id  |  parent_id  |  titel    |  datum      |
---------------------------------------------
1   |  0          |  tekst x  |  2006-09-10 |
2   |  1          |  hoi hoi  |  2006-10-09 |
3   |  0          |  tekst y  |  2006-09-11 |
4   |  3          |  doing    |  2006-10-11 |

gewenste resulaat:
1. tekst y  (laatste bericht 2006-10-11)
2. tekst x  (laatste bericht 2006-10-09)

en ik zoek naar een manier om alle records met parent_id = 0 te sorteren op de laatste datum van de records met DAT parent_id = id... haha ik weet niet zo goed hoe ik het moet omschrijven.

ik zit dus te denken aan een ORDER BY 'datum' maar hij moet dus de datum pakken van de 'child' en niet die van de 'parent'

ik wilde het dus makkelijk doen, door start-berichten en reacties te plaatsen in dezelfde tabel met een simpel parent_id idee, maar misschien dat ik toch de start-berichten en reacties dan in een aparte tabel moet zetten? want dan snap ik het verder wel...

[lier]

select titel from [tabelnaam] where parent_id = 0 order by datum desc ?

Edit:

Zie nu pas dat je de parents wil tonen op basis van de datum van de childs...

Nog heel even denken.

[ Voor 44% gewijzigd door lier op 11-10-2006 13:57 ]

[amoen]

dan sorteert ie dus op datum van de 'parent' en niet op die van de laatste child...
mmz misschien anders de datum van de parent updaten als er een nieuwe child is geplaatst?

dan los ik het probleem wel op...

edit:
ja je had het dus toch gezien

[ Voor 9% gewijzigd door amoen op 11-10-2006 13:59 ]

[lier]

Welk DBMS is hier van toepassing ?

Dit in verband met de SQL standaard...

Edit1:

Je kan natuurlijk je tabel voorzien van een extra kolom met date_last_update toevoegen.
Misschien niet zo heel netjes (in het kader van normaliseren), maar performancetechnisch gezien wel lekker snel.

[ Voor 58% gewijzigd door lier op 11-10-2006 14:03 ]

[P_de_B]

Een simpel GROUP BY met een MAX moet dit wel kunnen doen:

SQL:

SELECT t1.tekst, max(t2.datum) AS DatumLaatsteReactie
FROM Tabel t1
LEFT OUTER JOIN Tabel t2 ON t1.id = t2.parent_id
WHERE t1.parent_id = 0
GROUP BY t1.tekst

Uit Programming FAQ - SQL :
Hoe werken Joins
Hoe werkt Group By

[lier]

P_de_B schreef op woensdag 11 oktober 2006 @ 14:08:
Een simpel GROUP BY met een MAX moet dit wel kunnen doen:

SQL:

SELECT t1.tekst, max(t2.datum) AS DatumLaatsteReactie
FROM Tabel t1
LEFT OUTER JOIN Tabel t2 ON t1.id = t2.parent_id
WHERE t1.parent_id = 0
GROUP BY t1.tekst

Uit Programming FAQ - SQL :
Hoe werken Joins
Hoe werkt Group By

Was ook mijn eerste gedachte, maar dan neem je nog niet de mogelijkheid mee voor parents zonder kinderen.

Dit kan je oplossen met:

ISNULL(max(t2.datum) ,t1.datum) (ennuh...ORDER BY DESC toevoegen)

Vraag blijft of je het op deze manier op wil lossen (performance...), of is dit een huiswerk opdracht ?

[P_de_B]

Ik doe al een left outer join, dan komt er een NULL (= 'onbekend') voor datum laatste reactie als er geen reacties zijn. Dat lijkt me op zicht wel goed. Maar je hebt gelijkt. Met ISNULL kun je daar een andere waarde voor teruggeven.

Ik zou me trouwens met dit soort queries geen zorgen maken over performance. Het joinen van tabellen is juist de kracht van een RDBMS. Ik kan me situaties voorstellen dat je gaat denormaliseren omwille van performance, maar dan alleen bij erg zware queries, niet bij deze.

[ Voor 37% gewijzigd door P_de_B op 11-10-2006 14:15 ]

[amoen]

ok de sql van PdB werkt prima,

maar als ik daarbij de ISNULL bij stop, gaat het helaas mis

dit is mijn echte querie:

SQL:

SELECT a.*, ISNULL(MAX(b.dis_datum),a.dis_datum) FROM `discussie` a LEFT OUTER JOIN  `discussie` b ON (a.dis_id=b.dis_parent) WHERE a.dis_parent = '0' GROUP BY a.dis_id ORDER BY b.dis_datum DESC

en hij stopt na: "ISNULL(MAX(b.dis_datum)," « de komma?

[P_de_B]

Welke database gebruik je? Voor MySQL kun je ipv ISNULL COALESCE gebruiken. ISNULL is specifiek voor MSSQL.

edit:
LAAT MAAR: je gebruikt MySQL. De geweldige database die het toestaat bij een GROUP BY kolommen in je select op te nemen die geen aggregate funtie hebben, of voorkomen in de GROUP BY clausule.

Die SELECT a.* kan niet. Je moet dan ook de overige velden van a opnemen in de GROUP BY, of een aggregatie functie (SUM, MAX MIN etc) opnemen voor de velden die niet in de GROUP BY zitten.

[ Voor 64% gewijzigd door P_de_B op 11-10-2006 14:28 ]

[amoen]

aah vandaar, ik gebruik MYSQL,

chips... met die coalesce werkt het wel, maar bij het sorteren op b.dis_datum retourneerd ie 'natuurlijk' NULL bij de parent zonder childs! dus komt dat bericht automatisch onderaan te staan.

SQL:

SELECT a.dis_datum,b.dis_datum,a.dis_id, COALESCE(MAX(b.dis_datum),a.dis_datum) FROM `discussie` a LEFT OUTER JOIN  `discussie` b ON (a.dis_id=b.dis_parent) WHERE a.dis_parent = '0' GROUP BY a.dis_id ORDER BY b.dis_datum DESC

kan ik d.dis_datum niet vervangen met a.dis_datum als ie op NULL staat?

[ Voor 42% gewijzigd door amoen op 11-10-2006 14:37 ]

[amoen]

aha!

ik denk de oplossing gevonden te hebben met IFNULL:

SQL:

SELECT a.dis_datum,b.dis_datum,a.dis_id, IFNULL(MAX(b.dis_datum),a.dis_datum) as `sorteer` FROM `discussie` a LEFT OUTER JOIN  `discussie` b ON (a.dis_id=b.dis_parent) WHERE a.dis_parent = '0' GROUP BY a.dis_id ORDER BY `sorteer` DESC

door er een naam aan te geven: `sorteer` kan ik daar dan ook weer het resultaat op sorteren!
zien jullie nog gekke dingen?

zo niet, super bedankt voor de hulp op de goede weg!

[P_de_B]

Je moet a.dis_datum ook nog in de GROUP BY clausule opnemen of een een aggragatie functie omheen zetten, het werkt nu wel omdat het MySQL is, maar het is niet correct.