Thank you Igor. You've helped me before with what also turned out to be a similar select referencing the same table twice. I guess it's a concept that I don't fully get. If there is a name for this technique I'll go Google and study up on it.
Doug > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Igor Tandetnik > Sent: Thursday, September 09, 2010 10:59 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query suggestion? > > Doug <pa...@poweradmin.com> wrote: > > I'm wondering if the SQL gurus here can give me some direction. I > have a > > very simple stats table: > > > > CREATE TABLE StatData > > ( > > StatID INTEGER NOT NULL, > > Value REAL NOT NULL, > > Date INTEGER NOT NULL > > ); > > > > I'd like to pull out the most recent date and associated value for > each > > StatID. > > select StatID, Value, Date from StatData t1 where rowid = > (select rowid from StatData t2 where t2.StatID = t1.StatID > order by Date desc limit 1); > > -- or > > select StatID, Value, Date from StatData t1 where Date = > (select max(Date) from StatData t2 where t2.StatID = t1.StatID); > > The first query is probably slightly faster, the second is easier on > the eyes. > > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
