[MySQL] Laatste row per bepaald id

[Fl4sh3r]

Al enkele dagen zit ik met het volgende te worstelen. Op internet kan ik er niets over vinden en ook in #mysql op efnet hebben ze me nog niet echt kunnen helpen. Dat terwijl het volgens mij toch een gebruikelijk probleem is.

Ik probeer in MySQL5 uit een tabel de laatste regels te selecteren per service.
In de tabel zitten gegevens van services; een datum+tijd waarop de check is gedaan en de status (0=unavailable, 1=available)
Ik wil van iedere service de laatste status.

SQL:

SELECT service, MAX(checked), status FROM servicestatus GROUP BY service;

voldoet niet, omdat deze een willekeurig status pakt (in #mysql hadden ze het over 'status is floating')

Wat is de standaard oplossing in een dergelijk geval? En hoe kan ik het oplossen?

Mijn tabel:

SQL:

CREATE TABLE  `test`.`servicestatus` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `service` int(10) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  `checked` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Mijn data:

SQL:

INSERT INTO `servicestatus` (`id`, `service`, `status`, `checked`) VALUES 
(1, 80, 1, '2007-05-12 12:26:09'),
(2, 22, 1, '2007-05-12 12:26:09'),
(3, 80, 0, '2007-05-12 12:26:24'),
(4, 22, 1, '2007-05-12 12:26:24'),
(5, 80, 0, '2007-05-12 12:26:44'),
(6, 22, 0, '2007-05-12 12:26:44');

Ik wil in uit deze data dus terugkrijgen:
80 12:26:44 0
22 12:26:44 0

[NMe]

Dit heeft niets met ontwerp te maken maar juist heel veel met implementatie. Waar hoort mijn topic?

SEA>>PRG

[Pete]

Dit zou je kunnen doen met een join naar de eigen tabel (conditie: max(t1.checked) = t2.checked and t1.service = t2.service). t2.status is dan de status die bij de laatste tijd hoort

[Fl4sh3r]

phsmit bedankt voor je reactie, ik kom er toch nog niet helemaal uit. Wil je misschien je antwoord iets aanvullen?

SQL:

SELECT *
FROM servicestatus AS t1
JOIN servicestatus AS t2
ON (MAX(t1.checked) = t2.checked AND t1.service = t2.service)

Geeft mij de melding: "Invalid use of group function"

Dit klopt naar mijn idee wel, want MAX() mag natuurlijk alleen gebruikt worden i.c.m. GROUP BY.

[Verwijderd]

probeer eens:

SELECT a.service, MAX(a.checked), a.status
FROM servicestatus a
inner join (select max(checked) as checked from servicestatus) b on (a.checked= b.checked)
GROUP BY a.service, a.status

[P.O. Box]

kijk eens naar HAVING... dat is wat je zoekt....

hoef je ook geen joins te gebruiken

[ Voor 28% gewijzigd door P.O. Box op 12-05-2007 13:17 ]

[Verwijderd]

Verwijderd schreef op zaterdag 12 mei 2007 @ 13:16:
probeer eens:

SELECT a.service, MAX(a.checked), a.status
FROM servicestatus a
inner join (select max(checked) as checked from servicestatus) b on (a.checked= b.checked)
GROUP BY a.service, a.status

sorry bedoelde zoiets:


SELECT a.service, MAX(a.checked), a.status
FROM servicestatus a
inner join (select max(checked) as checked from servicestatus group by service) b on (a.checked= b.checked)
GROUP BY a.service, a.status

[Fl4sh3r]

Cashje, dat lijk idd precies te doen wat ik wil.

Ik ga 'm even rustig analyseren en zal hier posten mocht het toch niet werken.

Thanks

[MBV]

Dat zou ik niet doen: zoals Edwardvb al zegt, krijg je daardoor een vrij trage join die je kan vermijden door de HAVING constructie te gebruiken

[Fl4sh3r]

Hmm... hij lijkt helaas toch niet te werken...

Wanneer de `checked` voor iedere service niet precies gelijk is gaat het fout.

[Fl4sh3r]

Edward of MVB, kunnen jullie misschien iets meer zeggen over die HAVING?

Voor zover ik weet kun je HAVING gebruiken in bijvoorbeeld het volgende geval:
GROUP BY date
HAVING MAX(date) < 01-01-2007

Waarbij je in de HAVING altijd een group selection vergelijkt met een vaste waarde.

[P.O. Box]

Fl4sh3r schreef op zaterdag 12 mei 2007 @ 13:24:
Edward of MVB, kunnen jullie misschien iets meer zeggen over die HAVING?

Voor zover ik weet kun je HAVING gebruiken in bijvoorbeeld het volgende geval:
GROUP BY date
HAVING MAX(date) < 01-01-2007

Waarbij je in de HAVING altijd een group selection vergelijkt met een vaste waarde.

en als je dan < vervangt door =
en 01-1-2007 vervangt door date
dan ben je dus klaar

[Verwijderd]

waarom geeft deze query niet wat je wilt?

SELECT a.service, MAX(a.checked), a.status
FROM servicestatus a
inner join (select service,max(checked) as checked from servicestatus group by service) b
on (a.checked= b.checked and a.service=b.service)
GROUP BY a.service, a.status

[Fl4sh3r]

Die laatste van Cashje lijkt het inderdaad te doen.

Ik ga er nog even mee experimenteren. Bedankt allemaal voor zover.

[P.O. Box]

jaja... nou ja, als je er zelf tevreden mee bent, moet je dat vooral doen....

[Pete]

Ik had het net even mis. Ik dacht dat het zonder subquery kon, met enkel een join kon. Ik denk dat deze query nog net even iets effectiever is dan die van Cashje

SQL:

SELECT s1.service, s1.checked, s1.status
FROM servicestatus s1
WHERE (
s1.service, s1.checked
)
IN (

SELECT s2.service, MAX( s2.checked )
FROM servicestatus s2
GROUP BY s2.service
)

Over die Having. Dat zou er dan zo iets uit moeten zien:

SQL:

SELECT s1.service, MAX( s1.checked ) , s2.status
FROM servicestatus s1
JOIN servicestatus s2 ON ( s1.service = s2.service )
GROUP BY s1.service
HAVING MAX( s1.checked ) = s2.checked

edit: ho, stop. Die query met having gaat niet werken. Ik heb even iets over het hoofd gezien. Ik denk dat het niet mogelijk is met having. Indien het wel kan zou ik dat graag van de having-fans willen zien

[ Voor 13% gewijzigd door Pete op 12-05-2007 13:53 ]

[NMe]

Fl4sh3r schreef op zaterdag 12 mei 2007 @ 13:36:
Die laatste van Cashje lijkt het inderdaad te doen.

En die van Edwardvb, die veel beter performt, doet het niet?

[P.O. Box]

phsmit schreef op zaterdag 12 mei 2007 @ 13:50:
Over die Having. Dat zou er dan zo iets uit moeten zien:

SQL:

SELECT s1.service, MAX( s1.checked ) , s2.status
FROM servicestatus s1
JOIN servicestatus s2 ON ( s1.service = s2.service )
GROUP BY s1.service
HAVING MAX( s1.checked ) = s2.checked

dat kan nog simpeler volgens mij hoor.... zonder join...

[Pete]

-NMe- schreef op zaterdag 12 mei 2007 @ 13:50:
[...]

En die van Edwardvb, die veel beter performt, doet het niet?

Nee (denk ik)

Edwardvb schreef op zaterdag 12 mei 2007 @ 13:51:
[...]


dat kan nog simpeler volgens mij hoor.... zonder join...

Hoe dan?

(de reden dat ik overtuigd ben dat het niet met alleen having kan is de volgende. Zodra je de GROUP BY hebt gedaan op service, zijn er altijd count(service) rijen. Met Having kun je deze rijen filteren. Dat is iets wat je niet wilt, want je wilt alle services. Dus Having is hier niet nutttig (nog steeds "denk ik", ik weet het nog niet helemaal zeker))

[ Voor 65% gewijzigd door Pete op 12-05-2007 13:57 ]

[Verwijderd]

hmm, waarom is volgens jou een 'in' constructie beter??
leg eens uit, svp..

anyways, heb ff een executieplan van de twee varianten opgevraagd:

SELECT a.service, MAX(a.checked), a.status
FROM servicestatus a
inner join (select service,max(checked) as checked from servicestatus group by service) b
on (a.checked= b.checked and a.service=b.service)
GROUP BY a.service, a.status

vs
SELECT s1.service, MAX( s1.checked ) , s2.status
FROM servicestatus s1
JOIN servicestatus s2 ON ( s1.service = s2.service )
GROUP BY s1.service , s2.status, s2.checked
HAVING MAX( s1.checked ) = s2.checked

geeft:

41% tegen 59% query cost

variant 1 is dus beter

[P.O. Box]

ik heb het niet getest, dus misschien maak ik ergens een verschrikkelijke denkfout, maar ik zal hem dus maar even voorkauwen, omdat ik dit niet kan aanzien:

SQL:

SELECT service, status, checked
FROM servicestatus
GROUP BY service
HAVING checked = MAX(checked)

[Pete]

Verwijderd schreef op zaterdag 12 mei 2007 @ 14:00:
variant 1 is dus beter

Ik denk dat je dat nog steeds niet kunt zeggen. Heb je nl. al indexes gelegd? Trwns, hoe vraag jij een executionplan op? Als ik een explain do, dan zie ik geen "Query cost"? Of doe ik iets verkeerd?

[Verwijderd]

SELECT service, status, checked
FROM servicestatus
GROUP BY service
HAVING checked = MAX(checked)

werkt niet, tenmiste niet bij mij op dit voorbeeld

[Pete]

Edwardvb schreef op zaterdag 12 mei 2007 @ 14:05:
ik heb het niet getest, dus misschien maak ik ergens een verschrikkelijke denkfout, maar ik zal hem dus maar even voorkauwen, omdat ik dit niet kan aanzien:

SQL:

SELECT service, status, checked
FROM servicestatus
GROUP BY service
HAVING checked = MAX(checked)

Ik heb m getest, hij werkt niet. Je krijgt nu de rijen te zien waar (na de service-groupby) er toevallig dezelfde tijd staat als de max(checked) voor die rij. In dit specifieke voorbeeld vergeet de query om de rij service 80 te tonen

[Verwijderd]

phsmit schreef op zaterdag 12 mei 2007 @ 14:07:
[...]


Ik denk dat je dat nog steeds niet kunt zeggen. Heb je nl. al indexes gelegd? Trwns, hoe vraag jij een executionplan op? Als ik een explain do, dan zie ik geen "Query cost"? Of doe ik iets verkeerd?

heb geen indexes gelegd, maar denk niet dat dit enig verschil uitmaakt in dit geval.
waarom denk je dat?

[P.O. Box]

phsmit schreef op zaterdag 12 mei 2007 @ 14:11:
[...]


Ik heb m getest, hij werkt niet. Je krijgt nu de rijen te zien waar (na de service-groupby) er toevallig dezelfde tijd staat als de max(checked) voor die rij. In dit specifieke voorbeeld vergeet de query om de rij service 80 te tonen

hmmm, dan maak ik dus toch een gruwelijke denkfout.... maar ik zie hem nog niet

[Pete]

Ik denk wel zeker dat een index op service een performancewinst zal geven. Hoeveel; en bij welke query het meeste weet ik niet, maar dat is testen.

Maar je vergeet nu antwoord te geven op de vraag waar jij "Query cost" vandaan haalt?

[Verwijderd]

met behulp van zoiets:
http://dev.mysql.com/tech...g-new-query-profiler.html

[Verwijderd]

denk wel dat het winst geeft ja dat is logisch, maar t.o.v. de twee varianten is dat onbelangrijk en dus verwaarloosbaar.

of nu maak ik wellicht een denkfout

[Pete]

Ok, die was mij niet bekend (werk nog teveel met mysql4 denk ik )
Maar als ik het goed bekijk kijkt deze profiler nog steeds naar de query en de actuele data. Oftewel, het is dan nu nog niet duidelijk of met een reele dataset (dus 100'en-100000'en rijen) welke query beter performt

[Verwijderd]

als antwoord:

- zie mijn vorige post


p.s.
waarom is een in constructie beter volgens jou?
ben benieuwd naar je antwoord

[Pete]

Ik moet zoiezo zelf beter kijken, nu ik nl. goed kijk zie ik dat er weinig verschil zit tussen onze querys. Ipv de inner join van jouw heb ik alleen een in.
Als ik even probeer na te denken als de DB dan lijkt het mij dat de DB dit naar hetzelfde zou mogen omschrijven.
Als ik echter naar de explain kijk van beide queries heeft mijn query 2 stappen nodig en die van jouw 3. Daar staat tegenover dat de subquery van jouw derived is en die van mij dependent. Hoewel dit dan weer vreemd is (het zijn exact dezelfde subqueries) denk ik dat het weinig uithaalt.

Al met al, het was meer fingerspitzengefuhl (en misschien heeft dat gefuhl wel gelijk), maar met feiten is het niet te onderbouwen.