On Tue, Jul 1, 2008 at 2:53 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Csaba" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> 1) In the following LEFT JOIN, is it possible to alter >> the query so that there is no w. prefix at the >> beginning of each returned column name: >> >> SELECT w.* FROM Words AS w LEFT JOIN Words as w2 >> ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Rev<w2.Rev >> WHERE w2.Id IS NULL > > select * from Words where not exists ( > select * from Words w2 > where Words.Id = w2.Id and Words.Lang=w2.Lang and Words.Rev<w2.Rev > ); > > -- or > > select * from Words where Rev = ( > select max(Rev) from Words w2 > where Words.Id = w2.Id and Words.Lang=w2.Lang);
Clever. Thanks, I like them. If you do a SELECT * FROM tableName GROUP BY ... then the GROUP BY is forming equivalence classes among the data. For this reason, usual practise is to say that the SELECT clause must only include aggregate functions for those fields not in the GROUP BY. However, MySQL (at least this is what I recollect from about 4 years ago) takes the tack that it will provide a 'representative' row from each such class where no aggregate function is specified for a non GROUP BY field e.g. SELECT Id, Word FROM tableName GROUP BY Word would return one (effectively random, since I don't know the selection method) Id per each unique Word. I could achieve the same thing in SQLite by doing a group_concat with a comma separator and then slicing off prior to the first comma, but this is not so nice in general (escaping issues), and prevents me from doing a * as opposed to having to list each field explicitly. As this applies to my query, it would be: SELECT * FROM Words GROUP BY Id, Lang, Rev HAVING Rev=Max(Rev) Given that I have my Primary Key as (Id, Lang, Rev) it is (at least in theory) deducable by the database that each equivalence class is reduced to size 1 So the question is, would it make sense to suggest that SQLite take a member of the equivalence class when non GROUP BY fields are selected without aggregate functions or has this already been discussed and rejected? As far as I can see, there is no backwards compatibility issue. Csaba >> 3) Which of the following two queries is more efficient (Ie. is >> it better to have the w.Id=527 before or after the WHERE keyword)? > > I doubt it makes any difference. > > Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
