[sqlite] simple sql, or requires custom aggregate?

[Derrell . Lipman]

I'm having trouble generating what should be a simple sqlite query.  I need a
query that returns the ROW data corresponding to a GROUP BY and aggregate
restriction.  Here's an example:

    sqlite> create table t (t integer, x integer, y integer);
    sqlite> insert into t values (100, 10, 20);
    sqlite> insert into t values (101, 10, 21);
    sqlite> insert into t values (100, 20, 30);
    sqlite> select max(t), x, y from t group by x;
    max(t) = 100
         x = 20
         y = 30

    max(t) = 101
         x = 10
         y = 20
    sqlite>

Note that the second row returned is not an actual row from the table.  What I
really want returned there is the (101, 10, 21) row.

I haven't been able to come up with a HAVING clause that does what I want,
either.  Just doing HAVING MAX(t) evaluates to HAVING TRUE for each row with a
non-zero value of t, so all rows are selected.  I tried HAVING t = MAX(t) but
then I don't get every possible x value:

    sqlite> select max(t), x, y from t group by x having t = max(t);
    max(t) = 100
         x = 20
         y = 30
    sqlite>

I could probably solve this by writing a custom aggregate function that took
the rowid, x and t, and returned the rowid with the greatest t for the value
of x, but that seems unnecessary and overly complicated...

For clarification, what I want back from the query on the above table are two
rows: (101, 10, 21) and (100, 20, 30) which are the rows with the maximum
value of t for each value of x.

Any help greatly appreciated!

Derrell