Petite, well yes, the syntax you gave, subquery in the from clause, is
functionally equivalent to the one I gave; either way we are returning the foo
records that match the result of the subquery. In other words, I agree with
you, and could have written it the way you did, but I considered my choice less
verbose; in practice, one would more likely do it how you did, however. --
Darren Duncan
Petite Abeille wrote:
On Sep 9, 2012, at 6:51 AM, Darren Duncan <dar...@darrenduncan.net> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
