On Thu, Jul 3, 2008 at 1:33 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Csaba" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] >> SELECT * FROM Words >> GROUP BY Id, Lang >> HAVING Rev=Max(Rev) > > This wouldn't do what you think it does. HAVING clause applies to the > whole group, not to individual rows. It determines whether the group > makes it into final resultset. So this query would pick some random row > within the group, take Rev from it, and then keep or throw away the > group based on whether this random Rev just accidentally happens to be > the largest. The output of this query is unpredictable and, as far as I > can tell, useless. > > Igor Tandetnik
Thanks Igor. That's the same reason why it didn't work 4 years ago, and I wound up coming up with the LEFT JOIN (in those pre subquery days of MySQL) approach. Only I didn't consider my query very well just now, and missed that point till you pointed it out. I especially appreciate that SQLite is picking a representative element (I thought I had tested for this about two years back, but my memory is clearly not the thing to go by today). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
