Rhino wrote:
I can't test this in MySQL- I'm using an older version of MySQL that doesn't
support subqueries - but it works in DB2 and it should do the trick for
getting the current weight of each VBS_id value:
select VBS_id, date, weight
from VBS_table x
where date =
(select max(date) from VBS_table
where VBS_id = x.VBS_id);
I'm assuming you are using a more version of MySQL which DOES support
subqueries! If not, you may be able to get the same result with temp tables
but I haven't tried that. Make sure to say something if you don't know how
to use temp tables to simulate subqueries. I'm dubious that this query can
be simulated with temp tables though....
(Rant: I REALLY wish people (all people, not just you) posting questions to
this mailing list would get in the habit of specifying which version of
MySQL they are using!! The answers to questions FREQUENTLY depend on the
MySQL version so it would really help reduce the size of answers if people
volunteered this information in the original question.)
Also, I'm assuming that that MySQL supports correlated subqueries; I'm
really not sure so I'll let you try the actual query and see if it works for
you.
Here's an explanation of how this query works, in case you've never seen one
like this before.
This is called a correlated subquery; the key thing that makes it obvious
that this is a correlated subquery (in case you have to recognize one) is
that a correlation name, in this case 'x', appears in the FROM clause of the
outer query and again in the WHERE clause of the subquery. The subquery
appears in brackets in case you are not familiar with subqueries.
A correlated subquery works backwards to a normal subquery. In a normal
subquery, the subquery is executed first and is only executed once: its
result is plugged into the outer query which then executes just once as
well. In a correlated subquery, both the outer query and the subquery are
executed _repeatedly_, possibly MANY times each.
The outer query is executed in order to obtain one row, THEN the subquery is
executed to see if the row found by the outer query can be kept. In this
case, let's say that the outer query returned the first row of the table,
which has a VBS_id of 111111 and a date of '10/3/2005': the subquery
determines the maximum (most recent) date for any row that has same VBS_id
as the one just found by the outer query; if the maximum date differs from
the date found by the outer query, the outer query row is discarded and does
not appear in the final result set. In this case, the maximum date for
VBS_ID is 10/8/2005 which is not equal to the value found by the outer
query, so that row is discarded.
Having dealt with the first row of the outer query, the outer query executes
again and gets another row. Again, the subquery is executed to see if the
date is the same as maximum date for that VBS_id and again, the outer row is
only kept if its date matches the maximum date found by the subquery. And so
it goes, one row at a time, until the outer query has read every row of the
table; a single row of the outer query is obtained, then the subquery
determines if that row contains the maximum date for the VBS_id that was
just found by the outer query. The final result will contain only rows that
have the maximum dates for each VBS_id and will show the VBS_id, the maximum
date, and the weight at that date. That is the result you requested.
Bear in mind that a correlated query can perform much more poorly than a
regular subquery, although the optimizer sometimes has tricks that make the
performance quite tolerable.
There may be a solution that doesn't involve a correlated subquery but it's
not coming to me. However, others who are better at SQL may think of
something that does the job.
Rhino
Wow, excellent explanation. By the way, my apologies... I am using
MySQL 5.0.15. I would be interested in knowing how to use temp tables
to simulate subqueries though. I found out that the sql statement,
although it returns the correct result, does not work in a view because
MySQL Views does not allow subqueries.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
