Jump to content

Archived

This topic is now archived and is closed to further replies.

karassik

GROUP BY problem

Recommended Posts

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.