Re: MySQL Query

At 1:01 PM +0100 5/3/02, CM wrote:

SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, PaysPeriode.DateDebut,PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 20020207000000 AND Pays.RefPays = 1 ORDER BY Periode.libPeriode, PaysPeriode.DateDebut This is soo close to working but just not quite. The Access query just pulled out unique values for the Periode.LibPeriod field which was perfect. But the MySQL seems to pull them all out. I have tried GROUPING the result by the Periode.LibPeriode which gives me unique values in the field but I need the unique values with the highest startdate i.e. PaysPeriode.DateDebut but it always pulls out the first start date?

I'd add a Max() function with the Group By, like...

       SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode,

MAX(PaysPeriode.DateDebut), PaysPeriode.DateFin

       FROM Periode, PaysPeriode, Pays

       WHERE Pays.RefPays = PaysPeriode.Refpays

AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 20020207000000 AND Pays.RefPays = 1

       GROUP BY Periode.libPeriode

       ORDER BY Periode.libPeriode, PaysPeriode.DateDebut

I'm not certain why your Access query only pulled one record. From the skim, it seems as if it should pull all that match, since you didn't indicate you wanted only those distinctly, or a max value. Though often an Inner Join can act as a limiter as well, so that maybe why it worked. But anyway, the find all records, max or min of the value you want, group records (max, min, ave, count , etc. functions all REQUIRE the Group By, you'll generate an error if you forget it), then order them, should work. Alnisa -- ......................................... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org ......................................... Nonprofit Tech E-Update mailto:nonprofit-tech-subscribe@egroups.com ......................................... transforming nonprofits through technology .........................................