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:[EMAIL PROTECTED]
.........................................
transforming nonprofits through technology
.........................................
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
