Tim McDaniel wrote: > Given a table T like this: > > A B > --------- > 1 5 > 2 5 > 3 5 > > I need a query to give me just the row with the largest A value, > within a group defined by B.
Tim, The statement proposed by Lawrence (copied below) will work if your table has only these two columns, A and B. SELECT MAX(A) AS A , B FROM T GROUP BY B >From your question I got the imprssion you may have other columns as well. For a table t like this; a|b|c 1|5|6 2|5|7 3|5|8 10|6|7 4|6|10 3|2|5 4|2|6 7|6|13 An extended version of Lawrence's query gives; sqlite> select max(a), b,c from t group by b; max(a)|b|c 4|2|5 3|5|6 10|6|7 Which has the wrong values for the column c. It uses the value of c from the first record in each group. There is no record with values 4, 2, 5. You can get the correct rows with the following query which is similar to Eric's proposal except that it is combined into a single SQL statement. select t.* from t join (select max(a) as a, b from t group by b) as key where t.a=key.a and t.b=key.b; Which will give the rows t.a|t.b|t.c 3|5|8 10|6|7 4|2|6 This works, but I suspect there is probably a better way to do it. I hope this helps.
