Oops, I meant to post this on the list AND copy the person asking the question....
Rhino ----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]> To: "Gobi" <[EMAIL PROTECTED]> Sent: Friday, November 04, 2005 1:46 PM Subject: Re: Help with an SQL query > 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 > > > > ----- Original Message ----- > From: "Gobi" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Friday, November 04, 2005 3:05 AM > Subject: Help with an SQL query > > > > Not sure if this is the right place to ask. I have a table, Weight, > > with the following test data: > > > > idx VBS_ID Date Weight > > 1 111111 10/3/2005 11.5 > > 2 111111 9/5/2004 10 > > 3 111111 10/7/2005 11.51 > > 4 111111 10/8/2005 11.52 > > 5 111112 10/8/2005 10.5 > > 6 111112 10/1/2005 10.3 > > 7 111112 9/28/2005 10 > > > > What I would like to do is to get the most recent weight for each unique > > VBS_ID. So, in this case, I want a result of: > > > > 111111, 10/8/2005, 11.52 > > 111112, 10/8/2005, 10.5 > > > > Can anyone tell me how to do this? Thanks. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
