[MySQL] gegevens opvragen waar ID = max(ID) met group by

[casapira]

Ik heb een probleem. (goh, wat raar)

Ik heb een tabel (reports_sc) met onderstaande kolommen:
ID, report_id, status_id, user_id, ChangeDate

Wanneer de status wordt gewijzigd, wordt er niet een update-query maar een insert-query uitgevoerd. Dit is om te kunnen zien wie wanneer voor welk rapport, de status heeft veranderd. Dus om een geschiedenis op te kunnen vragen. Dat is allemaal geen probleem.

Nu moet hij alleen nog wel de goede status weergeven in de lijst van reports. that is, de status die als laatste bij report_id is meegegeven.

Bij MySQL 4.1 zou dit makkelijk kunnen omdat deze sub-query's ondersteund.
De code hieronder zou dan namelijk werken.

code:

select * from reports_sc where ID = (
      select max(ID) from reports_sc group by report_id
      )

Echter ik gebruik 4.0 nog. Ik kan niet naar 4.1 omdat het systeem in een professionele omgeving gebruikt gaat worden. 4.1 is nog in de gamma-release.

Als je de

code:

where ID = max(ID),

gebruikt, dan geeft mysql de fout: ERROR 1111: Invalid use of group function
(op zich heel logisch omdat hij niet weet of hij de group by hierbij moet meenemen of niet)


Heeft iemand enig idee hoe ik dit op kan lossen?


Overige info:
Win2k
Microsoft.Net framework versie 1.1.4322
Mysql 4.0.18
Internet Explorer Webcontrols versie 1.0

[ Voor 5% gewijzigd door casapira op 22-09-2004 15:06 ]

[pthn]

denk ik nou te simpel als ik zeg:

code:

select distinct report_id from reports_sc order by report_id, ID desc

??

[ Voor 7% gewijzigd door pthn op 22-09-2004 15:31 ]

[casapira]

ja je denkt blijkbaar te simpel

bij die query krijg je alleen de report_id's
Ik wil de juiste status erbij hebben. de status_id van de laatste update (de hoogste ID)

[ Voor 15% gewijzigd door casapira op 22-09-2004 15:35 ]

[pthn]

En dit ?

code:

select report_id, MAX(ID) from reports_sc GROUP BY report_id

[casapira]

ja die code selecteerd al wel de goede Max(ID) maar die moet ik dan als ID hebben.
Want als je nu doet

code:

select *, MAX(ID) from reports_sc GROUP BY report_id

dan geeft hij nog de verkeerde ID terug. (wel goede report_id uiteraard) Hij pakt een random ID. Dit staat Hier uitgelegd:

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Ik moet dus MySQL vragen om de rijen waar ID gelijk is aan MAX(ID). wanneer ik dit doe geeft hij alleen de waarden waar nog geen update voor is geweest. (alleen de eerste report dus)
Daar is die ID gelijk aan de MAX(ID)

[ Voor 18% gewijzigd door casapira op 22-09-2004 15:46 ]

[Gomez12]

Twee query's gebruiken. In mysql<4.1 is er geen andere oplossing. Tussenresultaten in een aparte tabel opslaan of naar je applicatie toehalen.

[casapira]

Ja en tussen die resultaten en de 2e query dan weer een join maken.
dat vreesde ik al. moet het dan een aantal keren veranderen en is een hoop werk.

maargoed thnx.

[pthn]

@TS:
Ik lees daar toch wat anders, d'r zit nl. wel een aggregate functie op "ID". Maar als je meer velden dan report_id wilt hebben, doen moet je het wel in 2 keer doen.

[ Voor 40% gewijzigd door pthn op 22-09-2004 15:58 ]

[Gomez12]

@ pthn : Wat jij zegt klopt zolang je het maar over 2 velden hebt. Neem volgende situatie eens.

code:

kolom1                kolom2            kolom3
1                          1                     1
1                          2                     2
1                          2                     3
9                          2                     7

Nu wil ik van alle waardes uit kolom de hoogste waarde uit kolom 2 en de bijkomende waarde uit kolom 3. Dan krijg je dus

code:

kolom1              kolom2           kolom3
1                       2                     ?
9                       2                     7

Wat moet er in plaats van het vraagteken komen te staan???

P.S. Ik kan kolom 3 dus niet met een aggegrate function schrijven omdat ik gewoon degene wil hebben die bij kolom 2 hoort, voor de rest niks.

[ Voor 10% gewijzigd door Gomez12 op 22-09-2004 16:04 ]

[pthn]

Ik had inderdaad niet gelijk in de gaten dat TS meer velden uit de DB wilde halen.

[Hoppie]

@TS:
Even voor mijn begrip: je zoekt de laatste wijziging voor een bepaalde report_id?

code:

SELECT 
  ID, 
  status_id,
  user_id,
  ChangeDate 
FROM 
  reports_sc 
WHERE 
  report_id = 4 
ORDER BY 
  ID 
DESC 
LIMIT 0, 1

Dit zou het juiste resultaat geven voor één specifieke report_id.
Nu wil je deze informatie hebben voor alle unieke report_ids?
Als subqueries niet kunnen, blijft er volgens mij inderdaad alleen het gebruik van meerdere queries over...

[casapira]

Ik zoek idd de laatste wijziging voor een bepaalde report_id.
en ja die query gaf idd het juiste resultaat voor 1 report_id


Ik wil ze idd voor alle unieke report_id's in 1 keer opvragen. Aangezien het aantal reports nogal in de getalen kan lopen (en het niet bij 4, 5, 6 of 10 blijft en dus niet hard kan programmeren) zal ik gaan werken met een subquery.

Heren bedankt maar weer, ik ben weer een stap verder, (niet dat ik er blij mee ben maargoed ) i know what to do.

[ Voor 25% gewijzigd door casapira op 22-09-2004 16:49 ]