Home > Archive > PHP SQL > April 2006 > Can it be done ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| McHenry 2006-03-28, 3:59 am |
| 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
| |
| strawberry 2006-03-28, 6:59 pm |
| I think this is right...
SELECT propertyid, MAX( valuationdate ) , valuation
FROM properties
GROUP BY propertyid
| |
| strawberry 2006-03-28, 6:59 pm |
| ....where, presumably, your primary key is a combination of both the
propertyid and the valuationdate.
| |
|
| On Tue, 28 Mar 2006 14:33:00 +0800, "McHenry" <mchenry@mchenry.com>
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
--
<s@guerril.la.ape> (to email, remove .ape)
--
| |
| Stefan Rybacki 2006-03-30, 9:58 pm |
| -----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/RAJ4nWRAHQdqYVyvt8hprc7h9hhrXhgCgod9d
PIJ8wmI1cqXLaqB2oLPDXYs=
=9w+t
-----END PGP SIGNATURE-----
| |
| Stefan Rybacki 2006-03-30, 9:58 pm |
| -----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+2zAfi6OoOdS6cYWYt9mWaUAC
eLIxM
qxJVMBYHK9acPKI70m/ftug=
=qwJI
-----END PGP SIGNATURE-----
| |
| laptop 2006-03-31, 3:59 am |
| 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" <stefan.rybacki@gmx.net> wrote in message
news:493m5jFmnechU2@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> strawberry schrieb:
> 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/RAJ4nWRAHQdqYVyvt8hprc7h9hhrXhgCgod9d
> PIJ8wmI1cqXLaqB2oLPDXYs=
> =9w+t
> -----END PGP SIGNATURE-----
| |
| laptop 2006-03-31, 3:59 am |
| Sorry, that should read "given that Strawberry's query...".
Confusing hunh?
"laptop" <laptopaliasathotmail.com> 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" <stefan.rybacki@gmx.net> wrote in message
> news:493m5jFmnechU2@individual.net...
>
>
| |
| Stefan Rybacki 2006-03-31, 9:58 pm |
| -----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
[color=darkred]
> ...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)
iD8DBQFELdncyeCLzp/JKjARAuuvAKCNL7BrtXuwJ/2Gu+mIt3xZQOvWGQCfVRHh
F/We8g3CinNfDrh+3EhxnuM=
=oMis
-----END PGP SIGNATURE-----
| |
| strawberry 2006-04-01, 7:58 am |
| 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
| |
|
|
| Stefan Rybacki 2006-04-01, 6:59 pm |
| -----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-----
|
|
|
|
|