Re: How do I do this query efficiently?

[Manuel Vacelet]

2006/11/16, Peter Brawley <peter.brawley@stripped>:
> 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.

Actually after I sent my email I tried to understand by myself and I'm
happy to see you confirm my reasoning.

Thank you Peter,
Cheers,
Manuel