Jump to content


Photo

GROUP BY problem


  • Please log in to reply
2 replies to this topic

#1 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 08 September 2003 - 05:26 PM

Hello,

I am having a problem with the following SQL:

SELECT gpms_clinic,  id_clsmnth, idclinic_clsmnth, MAX(clsmnth_clsmnth) AS clsmnth_clsmnth, scheDate_clsmnthFROM (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_clsmnthFROM (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

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 09 September 2003 - 09:32 PM

have you tried using ORDER BY for the date field?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 09 September 2003 - 10:31 PM

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_clsmnthFROM (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_clsmnthORDER 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]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users