GROUP BY problem

[karassik]

Hello,

 

I am having a problem with the following SQL:

 

SELECT gpms_clinic,  id_clsmnth, idclinic_clsmnth, MAX(clsmnth_clsmnth) AS clsmnth_clsmnth, scheDate_clsmnth

FROM (clsmnth_schedule INNER JOIN clinic_clinic ON id_clinic=idclinic_clsmnth)

GROUP BY idclinic_clsmnth, scheDate_clsmnth

 

It executes fine, but it also lists more than one set of clsmnth_clsmnth for idclinic_clsmnth.

 

When I change it to the following:

 

SELECT gpms_clinic,  id_clsmnth, idclinic_clsmnth, MAX(clsmnth_clsmnth) AS clsmnth_clsmnth, scheDate_clsmnth

FROM (clsmnth_schedule INNER JOIN clinic_clinic ON id_clinic=idclinic_clsmnth)

GROUP BY idclinic_clsmnth

 

I get one per idclinic_clsmnth, but the scheDate_clsmnth shows up from an older record.

 

Thanks,

 

Nishan

[effigy]

have you tried using ORDER BY for the date field?

[karassik]

It ends up it was a mySQL MAX problem. You have to go through a set up hurdles to get a associated MAX and even then I didn\'t get it perfect.

 

SELECT gpms_clinic,c1.id_clsmnth, c1.idclinic_clsmnth, DATE_FORMAT(c1.clsmnth_clsmnth,\'%M %Y\') AS clsmnth_clsmnth, c1.scheDate_clsmnth,c1.change_clsmnth

FROM (clsmnth_schedule AS c1 INNER JOIN clsmnth_schedule AS c2 ON c1.idclinic_clsmnth=c2.idclinic_clsmnth AND c1.id_clsmnth>=c2.id_clsmnth AND c1.scheDate_clsmnth>=DATE_FORMAT(NOW(),\'%Y-%m-%d\') INNER JOIN clinic_clinic ON id_clinic=c1.idclinic_clsmnth)

GROUP BY c1.idclinic_clsmnth

ORDER BY gpms_clinic ASC 

 

In this scenario, the date in scheDate has to be today or later for it to see that it is the MAX otherwise it will leave it out. Anyone else have a better way?

 

Nishan[/code]