Can it be done ?

I have a table with 3 columns, propertyid, valuationdate & valuation

I want to return the most recent valuation for each property, sounds simple
but it's not !!

Thanks

Re: Can it be done ?

I think this is right...

SELECT propertyid, MAX( valuationdate ) , valuation
FROM properties
GROUP BY propertyid

Re: Can it be done ?

....where, presumably, your primary key is a combination of both the
propertyid and the valuationdate.

Re: Can it be done ?

On Tue, 28 Mar 2006 14:33:00 +0800, "McHenry"
wrote:

>I have a table with 3 columns, propertyid, valuationdate & valuation
>
>I want to return the most recent valuation for each property, sounds simple
>but it's not !!

Not knowing what SQL engine you're using or how your table is defined,
try:

MySQL:

SELECT valuation FROM table WHERE propertyid = 123 ORDER BY
valuationdate DESC LIMIT 1

MSSQL et al:

SELECT TOP 1 valuation FROM table WHERE propertyid = 123 ORDER BY
valuationdate DESC

...hth

--
(to email, remove .ape)
--

Re: Can it be done ?

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

strawberry schrieb:
> I think this is right...
>
> SELECT propertyid, MAX( valuationdate ) , valuation
> FROM properties
> GROUP BY propertyid
>
That doesn't work because valuation is unpredictable and the fact that
mysql isn't throwing an error does not meant that this is a wrong query.

So better use the solutions from Shaun. And I do even know another
solution for the case that there is no LIMIT or TOP available ;)

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

iD8DBQFELKK1yeCLzp/JKjARAt/RAJ4nWRAHQdqYVyvt8hprc7h9hhrXhgCg od9d
PIJ8wmI1cqXLaqB2oLPDXYs=
=9w+t
-----END PGP SIGNATURE-----

Re: Can it be done ?

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

strawberry schrieb:
> ...where, presumably, your primary key is a combination of both the
> propertyid and the valuationdate.

Why? At least it should not be just the id because then the group by is
senseless with the result that there is just one date per id and you
will just return the table itself.

Regards
Stefan
>

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

iD8DBQFELKMPyeCLzp/JKjARAkBJAKDJ8U+2zAfi6OoOdS6cYWYt9mWaUACe LIxM
qxJVMBYHK9acPKI70m/ftug=
=qwJI
-----END PGP SIGNATURE-----

Re: Can it be done ?

What was Shaun's solution? I can't see it in the thread. I'm still a
beginner with this stuff myself but, given that my query does seem to return
the correct answer, why is it wrong?

Cheers.
"Stefan Rybacki" wrote in message
news:493m5jFmnechU2@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> strawberry schrieb:
>> I think this is right...
>>
>> SELECT propertyid, MAX( valuationdate ) , valuation
>> FROM properties
>> GROUP BY propertyid
>>
> That doesn't work because valuation is unpredictable and the fact that
> mysql isn't throwing an error does not meant that this is a wrong query.
>
> So better use the solutions from Shaun. And I do even know another
> solution for the case that there is no LIMIT or TOP available ;)
>
> Regards
> Stefan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.1 (MingW32)
>
> iD8DBQFELKK1yeCLzp/JKjARAt/RAJ4nWRAHQdqYVyvt8hprc7h9hhrXhgCg od9d
> PIJ8wmI1cqXLaqB2oLPDXYs=
> =9w+t
> -----END PGP SIGNATURE-----

Re: Can it be done ?

Sorry, that should read "given that Strawberry's query...".

Confusing hunh?


"laptop" wrote in message
news:442ce251$0$668$fa0fcedb@news.zen.co.uk...
> What was Shaun's solution? I can't see it in the thread. I'm still a
> beginner with this stuff myself but, given that my query does seem to
> return the correct answer, why is it wrong?
>
> Cheers.
> "Stefan Rybacki" wrote in message
> news:493m5jFmnechU2@individual.net...
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> strawberry schrieb:
>>> I think this is right...
>>>
>>> SELECT propertyid, MAX( valuationdate ) , valuation
>>> FROM properties
>>> GROUP BY propertyid
>>>
>> That doesn't work because valuation is unpredictable and the fact that
>> mysql isn't throwing an error does not meant that this is a wrong query.
>>
>> So better use the solutions from Shaun. And I do even know another
>> solution for the case that there is no LIMIT or TOP available ;)
>>
>> Regards
>> Stefan
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.2.1 (MingW32)
>>
>> iD8DBQFELKK1yeCLzp/JKjARAt/RAJ4nWRAHQdqYVyvt8hprc7h9hhrXhgCg od9d
>> PIJ8wmI1cqXLaqB2oLPDXYs=
>> =9w+t
>> -----END PGP SIGNATURE-----
>
>

Re: Can it be done ?

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

laptop schrieb:
> Sorry, that should read "given that Strawberry's query...".
>
> Confusing hunh?

The answer to your question "why is this query wrong" is,
that if you're using the GROUP BY clause your SELECT clause can just
contain the columns that are used in the GROUP BY clause and any other
column has to be used within an aggregat function like MAX, MIN, COUNT etc.
That it works without aggregate function in mySQL is true but its like
using an aggregate function like GIVERANDOMVALUE out of the column. The
result is unpredictable and that it worked in your case is just by
accident. That can be different in another version or might be different
on another computer or OS.

Do you like an example? See this table

NO. name
1 Stefan
1 laptop
1 s

So what I do now is this:

SELECT name FROM table GROUP BY NO.

So what do you think which name appears at the end? Think about it ...


Regards
Stefan

> ...
>>

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

iD8DBQFELdncyeCLzp/JKjARAuuvAKCNL7BrtXuwJ/2Gu+mIt3xZQOvWGQCf VRHh
F/We8g3CinNfDrh+3EhxnuM=
=oMis
-----END PGP SIGNATURE-----

Re: Can it be done ?

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

Re: Can it be done ?

....which, I should add, is just a rewrite of techonthenet's answer to a
very similar problem.

See http://www.techonthenet.com/sql/max.php

Re: Can it be done ?

-----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 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/U1UKRACg n2/C
b8SJEOt0c7Scd7ShkJcAXZE=
=ro6H
-----END PGP SIGNATURE-----