I'm glad the explanation helped. I figured that the solution alone wouldn't
be very useful if it wasn't explained since it is not obvious to most people
how correlated queries work :-)
I really wasn't trying to center you out with my "rant" about MySQL version.
It's been a long-running frustration for me; I've tried to urge people to
give this information in their notes to the list but an awful lot of people
never think to mention their version. Then, an already lengthy reply gets
even longer as you try to explain: "If you have Version 3.x, the solution is
this.... If you have Version 4.x the solution is this..... If you have
Version 5.x, the solution is this....." You get the picture.
Okay, let me explain the temp tables approach now.
Most subqueries are not correlated so the subquery gets done once and then
its result gets plugged into the outer query. For example, given a table
called Employee in the Sample database which has one row for each employee
and each row contains an employee number, last name, and salary, this query
will return the employee number and last name of every employee who makes
more than the average salary for all employees in the table:
select empno, lastname
where salary >
If subqueries are supported, as in Version 4.1.x or above of MySQL, you just
run that exact query and you will get a list of all of the people who make
more than the average salary.
However, if you are using an older version of MySQL which doesn't support
subqueries, you need to use a temporary table approach. Basically, you
create a temporary table that contains the same results that the subquery
would have given you, then join it to the outer query. [Many, but not all,
subqueries can be rewritten as joins.] For example:
#Create the temporary table that will contain the average salary for all
create table if not exists temp_average_salary (average_salary
#Populate the temporary table with the average salary for all employees.
insert into temp_average_salary(select avg(salary) from Sample.Employee);
#Do a join of the employee table and the temporary table to obtain only
employees who make
#more than the average salary.
select empno, lastname, salary
from Sample.Employee e inner join temp_average_salary t
where e.salary > t.average_salary;
This would give the same result as the original solution that used
subqueries although there is obviously more work to do in accomplishing the
Initially, I was almost certain that this approach could not be made to work
for your problem due to its correlated nature but a couple of experiments
persuaded me that it was possible after all. It's actually quite similar to
the example I gave above, except that this time the temporary table is not
just a single value on a single row but a table containing one row for each
VBS_id, showing the max (latest) date for any row containing that VBS_id.
Here's the definition of the table and the correct method of populating the
create table if not exists temp_table
(VBS_id int not null,
max_date date not null);
insert into temp_table (select VBS_id, max(date) from VBS_table group by
If you display the contents of the temporary table, you get:
Now, you simply join the VBS_table and the temp_table, as follows:
select v.VBS_id, v.date, v.weight
from VBS_table v inner join temp_table t
on v.VBS_id = t.VBS_id
and v.date = t.max_date;
The result is exactly what you wanted:
VBS_id date weight
111111 2005-10-08 11.52
111112 2005-10-08 10.50
You could apply this solution to versions of MySQL that don't have subquery
support; just remember that you need to re-create the temp_table immediately
before you do the join each time; otherwise, you are going to determine
whatever weights were in effect whenever you first populated the temp_table,
not the current weights.
In your case though, you can make life better for yourself. Since you have
view support, you can simply create a view that is the equivalent to the
temp_table and then join the view to the VBS_table to get the data you want.
Since the view is always up-to-date, you eliminate the need to have the
temp_table at all. Something like this:
create view VBS_max_dates
as select VBS_id, max(date)
group by VBS_id;
select v.VBS_id, v.date, v.weight
from VBS_table v inner join VBS_max_dates z
on v.VBS_id = z.VBS_id
and v.date = z.max_date;
Note that the view is only created once, not each time you are about to do
One other solution jumps to mind that should be just as good. Instead of
creating temp tables or views, why not just put the original correlated
subquery that I gave you into a stored procedure? Then, all you'd need to do
is call that stored procedure every time you need that result.
----- Original Message -----
From: "Gobi" <gobi@stripped>
Sent: Saturday, November 05, 2005 8:05 AM
Subject: Re: Help with an SQL query
> Rhino wrote:
> >I can't test this in MySQL- I'm using an older version of MySQL that
> >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
> >but I haven't tried that. Make sure to say something if you don't know
> >to use temp tables to simulate subqueries. I'm dubious that this query
> >be simulated with temp tables though....
> >(Rant: I REALLY wish people (all people, not just you) posting questions
> >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
> >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
> >Here's an explanation of how this query works, in case you've never seen
> >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
> >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
> >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
> >as the one just found by the outer query; if the maximum date differs
> >the date found by the outer query, the outer query row is discarded and
> >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
> >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
> >only kept if its date matches the maximum date found by the subquery. And
> >it goes, one row at a time, until the outer query has read every row of
> >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
> >have the maximum dates for each VBS_id and will show the VBS_id, the
> >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
> >performance quite tolerable.
> >There may be a solution that doesn't involve a correlated subquery but
> >not coming to me. However, others who are better at SQL may think of
> >something that does the job.
> 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/mysql?unsub=1
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005