"Csaba" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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.
SQLite does the same thing. The problem with this is, when you have a query like select Word, Max(Rev) from Words; you usually want the word from the row with the largest Rev. But this query doesn't guarantee that - it returns the largest Rev, but Word may come from any random row. > As this applies to my query, it would be: > SELECT * FROM Words > GROUP BY Id, Lang, Rev > HAVING Rev=Max(Rev) This doesn't make any sense. You only have one row per group (Id, Lang, Rev being the primary key), and Max(Rev) is calculated across each group, so Rev=Max(Rev) is going to be always true. > 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 Correct. At which point the HAVING clause becomes moot. Am I missing something obvious? > 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? I don't know whether this has been discussed, but it has been accepted and implemented. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
