[MySQL] Group by query

[orf]

Ik kom niet uit een SQL query die simpel zou moeten zijn.
Ik heb twee tabellen:

Tabel bedrijven

id	naam	online
1	Google	1
2	Microsoft	0
3	Sun	1

Tabel bedrijven_nieuws

id	bedrijf_id	titel	bericht	datum
1	1	Google aandelen omhoog	...	2007-11-15
2	1	Google aandelen omlaag	...	2007-11-14
3	2	Mircrosoft aandelen omlaag	...	2007-11-14
4	3	Sun aandelen omlaag	...	2007-11-15
5	3	Sun aandelen omhoog	...	2007-11-14

Nu wil ik de laatste nieuwsberichten tonen, maar één per bedrijf en wel het nieuwste bericht van het bedrijf. De query zou op basis van bonvenstaande tabelinhoud het volgende moeten geven:

bedrijfsnaam	titel	bericht	datum
Google	Google aandelen omhoog	...	2007-11-15
Sun	Sun aandelen omlaag	...	2007-11-15

(meegenomen dat het bedrijf Microsoft niet online is.)


Als je alleen naar de nieuwsberichten kijkt, dan kom ik tot de volgende query:

SQL:

SELECT      id,
            titel,
            bericht,
            MAX(datum) AS datum
FROM        bedrijven_nieuws
GROUP BY    bedrijf_id, id, titel, bericht
ORDER BY    datum DESC

Ik wil de berichten gegroepeerd op bedrijf_id, maar alle kolommen die in de SELECT staan zonder aggregatie moeten in de GROUP BY.

Met die kolommen krijg ik echter dubbele bedrijf_id in de resultset.

Met subqueries heb ik geprobeerd de bedrijfsnaam bij de berichten te zoeken, maar als het simpele groeperen al mislukt schiet dat niet erg op.

Wat doe ik fout en snap ik GROUP BY toch niet helemaal?

[EfBe]

zoiets?

SQL:

SELECT  b.naam, bn.titel, bn.bericht, bn.datum
FROM    bedrijven b INNER JOIN bedrijven_nieuws bn
        ON b.id = bn.bedrijf_id
WHERE   bn.id = 
        (
            SELECT  id 
            FROM    bedrijven_nieuws bns
            WHERE   bns.bedrijf_id = b.bedrijf_id
            ORDER BY datum DESC
            LIMIT 1
        )
        AND b.online=1

(niet getest op MySql specific lameness)

[Sepio]

Join de nieuws tabel met het laatste nieuws bericht.

SQL:

select a.naam, b.titel, b.bericht, b.datum 
from  bedrijven a
inner join bedrijven_nieuws b
on a.id = b.bedrijf_id
inner join (
    --Haal het laatste bericht per bedrijf op.
    --LET OP: Sorteer dus niet op datum maar op id
    select bedrijf id, max(id) as id                                                
    from bedrijven_nieuws
    group by bedrijf id
) c 
on b.id = c.id
where a.online = 1

[ Voor 7% gewijzigd door Sepio op 15-11-2007 12:57 ]

[leuk_he]

[quote]EfBe schreef op donderdag 15 november 2007 @ 12:42:
zoiets?

SQL:

            LIMIT 1

(niet getest op MySql specific lameness)

dat is dat dus....


Je hebt geen group by nodig.

Je hebt een subquery (ook als inner join..) nodig die het laatste bericht per bedrijf uit de hoofd query filtert.

Echter de subquiery

Select max(datum) from berichten where parent.bedrijfid=bedrijfid kan ook nog eens matchen met meerderde berichten in je hoofdquery dus in dat geval zul een sub-sub query of implementatie afhankelijke limiet moeten maken tenzij je net als hierboven aanneemt dat de hoogste id de meeste recente zijn.

levert op

code:

SELECT     b.naam, bn.titel, bn.bericht, bn.datum
FROM    bedrijven b 
              bedrijven_nieuws bn
WHERE bn.bedrijf_id =b.id
and b.oneline=1 
and bn.id = (select max(bn1.id) 
                  from       bedrijven_nieuws bn1
                   where  bn1.bedrijf_id =b.id
                   and     bn1.datum = (select max (bn2.datum) where
                                                  from   bedrijven_nieuws bn2
                                                  where bn2.bedrijf_id =b.id))

(oracle smaak, wellicht zou ik daar rank by gebruiken om de 2e subquery te verwijderen?)

[orf]

zoiets?

Super, bedankt!
Het limitten op 1 record in de subquery klinkt logisch en ga ik onthouden.

Join de nieuws tabel met het laatste nieuws bericht.

max(id) hoeft niet persé het bericht te geven met de hoogste datum; het kan gebeuren dat een datum van een bestaand record wordt aangepast. Klopt het dat dan niet de hoogste datum wordt getoond?

[EfBe]

MAX() is hetzelfde als TOP 1 + order by desc. Deze is veelal makkelijker te optimizen voor een RDBMS

[orf]

Maar, max(id) hoeft niet max datum te zijn toch?

[djiwie]

Nee, MAX(id) geeft het nieuwsbericht met het hoogste id, selbstverstandlich
Je kunt wel MAX(datum) gebruiken natuurlijk. Of TOP 1 icm ORDER BY datum DESC, zoals EfBe voorstelt