Replying to my own post, sorry :-) Being pedantic here, when I said the "correct" way of doing what is required was...
> select * from t > where (a, b) in (select max(a), b from t group by b); that would work for ORACLE, but is not ANSI as such. The ANSI method would be... select * from t x where a in (select max(a) from t y where x.b = y.b); However - that does not work under SQLite either :-( You are stuck with the "hashing" method described in my answer. If the tables are exceptionally large, and you would need indexing to kick in to perform the query you may think of adding an extra column containing the hashed value and populate it prior to performing the query.... update t set ab = (b * 100000) + a; select * from t where ab in (select (b * 100000) + max(a) from t); That would use an index on ab in a lot of SQLs, I do not know about SQLite. On Tue, 2004-10-05 at 01:55, Eddy Macnaghten wrote: > > SELECT MAX(A) AS A, B, C > > FROM T > > GROUP BY B > > > > This is an invalid SQL statement (SQLite should generate an error here). > > The correct(tm) way to do this is with subqueries. > > ORACLE SQL (and others), using subqueries, you would use... > > select * from t > where (a, b) in (select max(a), b from t group by b); > > However - SQLite does not support that (two elements in an "in" clause) :-( > > Can you hash the two columns to give one as such (or similar)?.... > > > > > select * from t > where ((a * 100000) + b) in (select (a * 100000) + max(b) > from t group by b); > > > > which should give you what you want if you can. > > > Eddy > -- Edward A. Macnaghten http://www.edlsystems.com
