From: Peter Brawley
Date: November 16 2006 4:51pm
Subject: Re: How do I do this query efficiently?
List-Archive: http://lists.mysql.com/mysql/203381
Message-Id: <455C9729.90107@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Michael,

 >SELECT t1.id, t1.version, t1.value
 >FROM data t1
 >LEFT JOIN data t2 ON t1.id=t2.id AND t1.version < t2.version
 >WHERE t2.id IS NULL;
 >I had almost the same problem and I found this solution very smart...
 >even smarter than I can understand :)
 >Can someone explain to me why/how this query works ?

A two-step:
1. The left join creates a logical table with t1.ids on the left, and on 
the right ...
    (a) matching t2.ids, when t1.version<t2.version, or
    (b) nulls when t1.version>=t2.version.
2. The where clause removes (a), leaving only the t1.ids for which 
there's no greater version.

PB


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.6/536 - Release Date: 11/16/2006