Posted by Stefan Rybacki on 04/01/06 17:29

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

strawberry schrieb:
> OK, so the correct answer should be:
>
> SELECT pr.propertyid, pr.valuationdate, pr.valuation
> FROM properties pr, (
>
> SELECT max( valuationdate ) AS maxdate, propertyid
> FROM properties
> GROUP BY propertyid
> )maxresults
> WHERE pr.propertyid = maxresults.propertyid
> AND pr.valuationdate = maxresults.maxdate
>
Yes, or you can use the subselect in the where clause itself.
The question is, what RDBMS is the OP using. If it is <MySQL 4.1
subselects are not supported for this case I would use something like this:

SELECT p1.propertyid, p1.valuationdate, p1.valuation FROM
properties p1 LEFT JOIN
properties p2 ON (p1.propertyid=p2.propertyid AND
p2.valuationdate>p1.valuationdate)
WHERE p2.valuationdate IS NULL

This can return more than one result for any id if there are more than
one entries with max(valuationdate) and the same id, So probably you
have to do a group by in this case.

Regards
Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)

iD8DBQFELo4uyeCLzp/JKjARAhuvAJ44IkBFCRwyWhjpMX5J2tP/U1UKRACgn2/C
b8SJEOt0c7Scd7ShkJcAXZE=
=ro6H
-----END PGP SIGNATURE-----

• Next in forum: select * where a=1 if b=3
• Prev in forum: mysql_num_rows HELP
• Thread view: Re: Can it be done ?

[Reply to this message]