On Sep 9, 2012, at 6:51 AM, Darren Duncan <[email protected]> wrote:
> You will need to use a subquery to do what you want, because you want to do a
> join on the results of a group by. This is one example of syntax:
>
> select * from audtbl where (RowID, ChangeDate) in
> (select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);
As far as I know, such syntax (match on multiple 'in' columns) is not supported
by SQLite.
However this can easily be turned into a simple self-join:
select *
from foo
join (
select id,
max( date ) as date
from foo
group by id
)
as bar
on bar.id = foo.id
and bar.date = foo.date
As mentioned by Igor, there was a recent thread about this exact topic with
various other approaches:
http://thread.gmane.org/gmane.comp.db.sqlite.general/76558
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
