List:General Discussion« Previous MessageNext Message »
From:Manuel Vacelet Date:November 17 2006 9:17am
Subject:Re: How do I do this query efficiently?
View as plain text  
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
Thread
How do I do this query efficiently?Sebastiaan van Erk13 Nov
  • Re: How do I do this query efficiently?Peter Brawley13 Nov
    • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
    • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
      • Re: How do I do this query efficiently?Peter Brawley13 Nov
        • Re: How do I do this query efficiently?Rolando Edwards13 Nov
        • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
Re: How do I do this query efficiently?Peter Brawley16 Nov
  • Re: How do I do this query efficiently?Manuel Vacelet17 Nov