Quoting Ernest E Vogelsinger <[EMAIL PROTECTED]>:
> At 04:57 18.11.2002, Lars Espelid said:
> --------------------[snip]--------------------
> >I tought this would work, but it won't:
> >
> >SELECT * FROM identifisering as i
> >WHERE i.identifiseringid=(SELECT MAX(ii.identifiseringid)
> >FROM identifisering as ii
> >GROUP BY dprosjekt);
> >
> >also tried:
> >
> >SELECT * FROM identifisering as i
> >WHERE i.identifiseringid IN (SELECT MAX(ii.identifiseringid)
> >FROM identifisering as ii
> >GROUP BY dprosjekt);
> --------------------[snip]--------------------
>
> The only thing I can say the second form (using IN) works like a charm in
> PostgreSQL:
>
> select * from identifisering as i
> where i.identifiseringid in (
> select max(ii.identifiseringid) from identifisering as ii
> group by ii.dprosjekt
> );
>
> If it doesn't work in MySQL it must be one of the unfortunately numerous
> shortcomings of MySQL. I always suggest using PostgreSQL in favour of
> MySQL; it's OS as well, it's mature, it's fast (faster than MySQL AFAIK),
> etc etc. And it comes bundled with at least RedHat, but I believe it's
> available with most Linux distros.
If you seek many records, use _IN_ (will also work with one record). If you are
indeed seeking one record, the GROUP BY seems out of place.
Also in my experience with MSSQL, the SELECT MAX() syntax almost always requires
that you assign that result to a name.
ie:
SELECT * FROM identifisering as i
WHERE i.identifiseringid = (
SELECT MAX(ii.identifiseringid) AS maxIdentifiseringid
FROM identifisering as ii
)
GROUP BY dprosjekt
Now that I look at it, your GROUP BY was definitely out of place, since
dprosjekt no doubt is a col being returned from the SELECT * of the main query.
--
Ryan T. Gallagher
[EMAIL PROTECTED]
International Studies Abroad
http://www.studiesabroad.com
(512)480-8522
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
