[MySQL] query probleem met joins order/group by

[Verwijderd]

Heren,

Ik loop tegen een vervelend probleem aan. Het is weer even geleden dat ik wat complexere mysql queries heb geschreven, dus die kennis is wat roestig momenteel.

Zit met het volgende:

Tabel: LogFiles

log_name	varchar (32)
log_size	int (11)
log_status	int (11)

weblog_001	17	3
weblog_002	88	1
weblog_003	121	1

Tabel: LogUpdates

update_file	varchar (32)
update_status	int (11)
update_date	int (11) (unix timestamp)

weblog_001	6	123456789
weblog_001	1	123456799
weblog_001	3	123456999

Wat wil ik terug krijgen

Ik wil een query doen die de volgende logica bevat:

Selecteer alle LogFiles waar log_size < 20 waarbij vervolgens de laatste twee status changes

update_file	update_status_old	update_status_old_date	update_status_new	update_status_new_date
weblog_001	1	123456799	3	123456999

[Creepy]

En wat heb je zelf al geprobeerd en welke resultaten kreeg je daar dan uit? Heb je de PRG FAQ al eens bekeken? In het SQL deel daarvan staan JOINS en GROUP BY prima uitgelegd namelijk.

Nu dump je een vraag en that's it. We verwachten hier wat meer eigen inzet. GoT is nog steeds niet bedoeld als helpdesk om even snel een antwoord te kunnen scoren.

[ Voor 31% gewijzigd door Creepy op 14-08-2009 13:13 ]

[Verwijderd]

Ik had het idee dat mijn uitgebreide voorbeeld met tabelinhoud wel aangaf dat ik me er niet zo makkelijk van af wil maken, maar goed, laatste query (waarbij nog geen join is gemaakt met de andere tabel was als volgt):

code:

SELECT 
old.` update_file` AS `update_file`, 
old.`update_status` AS `update_status_old`, 
old.`update_status` AS ` update_status_old_date`, 
new.* 
FROM ` LogUpdates` `old`,` LogUpdates` `new` 
WHERE `old`.` update_file` = `new`.` update_file`  
AND `new`.`update_status` != `old`.`update_status`  
AND `new`.`update_date` > `old`.`update_date` 
GROUP BY `new`.` update_file` 
ORDER BY `new`.`update_date` DESC

Echter krijg ik niet netjes de laatste twee status updates, maar ik krijg de status 6 en 1 terug, in plaats van 3 en 1.

[H004]

Komt waarschijnlijk omdat je geen aggregeerfuncties gebruikt in je SELECT-gedeelte. Nu raadt MySQL maar een beetje welke data hij terug moet geven. Eigenlijk zou je een keiharde foutmelding moeten krijgen, maar tsja... mysql...

Programming FAQ - SQL

[ Voor 0% gewijzigd door H004 op 14-08-2009 13:35 . Reden: typo ]

[cariolive23]

Gooi GROUP BY weg, er is in geen velden of wegen een aggragate functie te bekennen, en gooi alle backticks ` weg. Die voegen niets toe, tenzij jij verboden namen gaat gebruiken. Spaties in tabel- en/of kolomnamen zijn ook bijzonder, geen idee waarom je dat zou willen doen.

En schrijf de JOIN's uit met JOIN, dat is eenvoudiger en overzichtelijker dan de boel in de WHERE zetten.

[Verwijderd]

Misschien met MAX proberen op de datum of een subquery

[ Voor 80% gewijzigd door Verwijderd op 14-08-2009 13:44 ]

[CH4OS]

cariolive23 schreef op vrijdag 14 augustus 2009 @ 13:35:
Gooi GROUP BY weg, er is in geen velden of wegen een aggragate functie te bekennen, en gooi alle backticks ` weg. Die voegen niets toe, tenzij jij verboden namen gaat gebruiken. Spaties in tabel- en/of kolomnamen zijn ook bijzonder, geen idee waarom je dat zou willen doen.

Ik zie niet in waarom de backticks weg zouden moeten, dat is toch gewoon standaard SQL? Ik gebruik de backticks ook altijd, speciaal daarom.
Dat het voor MySQL niet hoeft en in die zin dus 'forgiving' is waardoor het niet per se hoeft is mooi, maar als je SQL 'strict mode' gebruikt moeten ze volgens mij weer wel.

[whoami]

GJtje schreef op vrijdag 14 augustus 2009 @ 13:53:
[...]
Ik zie niet in waarom de backticks weg zouden moeten, dat is toch gewoon standaard SQL?

Backticks zijn helemaal geen standaard SQL.
Backticks zijn er alleen maar om duidelijk te maken dat evt reserved words/keywords niet als keyword moeten beschouwd worden in MySQL.
Maw, dit is iets MySQL specifiek.
In SQL Server gebruik je daarvoor bv blok-haken.

Zowiezo zie ik ook niet direct waarom je een group by zou nodig hebben.

Verder: de output die je wenst (zie je TS) zomaar rechtstreeks uit de DB krijgen, in die layout zal een beetje moeilijk gaan (is wellicht wel mogelijk, maar dan misbruik je de taal SQL voor iets waar het niet voor gemaakt is).
Ik zou dus gewoon de informatie mbhv SQL uit de DB trekken, en dan mbhv je programmeer of script taal die gegevens in het gewenste output formaat gieten.

[ Voor 33% gewijzigd door whoami op 14-08-2009 14:24 ]

[cariolive23]

Ik zie niet in waarom de backticks weg zouden moeten, dat is toch gewoon standaard SQL?

Integendeel! "MySQL" en "SQL standaard" is toch al iets wat niet goed samengaat... Wanneer je het volgens de standaard wilt doen, dan gebruik je dubbele quotes ". Maar ja, dan gaat het weer fout in MySQL, die maakt geen onderscheid tussen dubbele en enkele quotes rondom een string.

Maar wat je ook gebruikt, backticks en andere rotzooi rondom kolom- en/of tabelnamen is volkomen overbodig. Mocht het tóch nodig zijn, dan duidt het vrijwel altijd op fouten in de keuzes voor de namen van deze objecten. Los die fouten dan ook op, daar heb je veel meer aan.

[Cousin Boneless]

Heren,

Omdat vrouwen hier niet mogen antwoorden, geef ik twee oplossingen.
De eerste is wat korter, maar volgens mij minder goed te optimaliseren:

SQL:

select l.update_file, l.maxdate, lmax.update_status, l.prevdate, lprev.update_status
from (
  select l1.update_file, max(l1.update_date) as maxdate, max(l2.update_date) as prevdate
  from logupdates l1
  inner join logupdates l2 on l2.update_file = l1.update_file
  where l2.update_date < l1.update_date
  group by l1.update_file
) l
inner join logupdates lmax on lmax.update_file = l.update_file and lmax.update_date = l.maxdate
inner join logupdates lprev on lprev.update_file = l.update_file and lprev.update_date = l.prevdate
where l.update_file in (select log_name from logfiles where log_size < 20)

En wat langer, maar mogelijk beter te optimaleren:

SQL:

select l.update_file, l.maxdate, lmax.update_status, l.prevdate, lprev.update_status
from (
  select l2.update_file, l1.maxdate, max(l2.update_date) as prevdate
  from logupdates l2
  inner join (
    select update_file, max(update_date) as maxdate
    from logupdates
    group by update_file
  ) l1 on l1.update_file = l2.update_file
  where l2.update_date < l1.maxdate
  group by l2.update_file, l1.maxdate
) l
inner join logupdates lmax on lmax.update_file = l.update_file and lmax.update_date = l.maxdate
inner join logupdates lprev on lprev.update_file = l.update_file and lprev.update_date = l.prevdate
where l.update_file in (select log_name from logfiles where log_size < 20)

Vergelijkbaar probleem: http://gathering.tweakers.net/forum/list_messages/1355913

[ Voor 6% gewijzigd door Cousin Boneless op 16-08-2009 00:42 ]

[NMe]

cariolive23 schreef op vrijdag 14 augustus 2009 @ 13:35:
Die voegen niets toe, tenzij jij verboden namen gaat gebruiken.

` update_file`

[cariolive23]

Had deze fout al gezien, zie ook de volgende constructie:

old.` update_file` AS `update_file`,

Het meest grappige is nog dat de update_file mét spatie ook wordt opgehaald:

new.*

Want daarin komt

`new`.` update_file`

voor, waar geen alias wordt aangemaakt.

Ga vouten herstellen, ducktape-plakken is geen oplossing, dat is wachten op het volgende probleem.

[ Voor 4% gewijzigd door cariolive23 op 16-08-2009 09:43 ]