Re: [sqlite] Query suggestion?

[Doug]

Thenk you Gerry.  

After some studying I now understand that the inner SELECT is executed 
for each outer row -- so trimming the outer result set early seems 
like a very good optimization idea.

Doug

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Gerry Snyder
> Sent: Thursday, September 09, 2010 1:52 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Query suggestion?
> 
>   On 9/9/2010 11:32 AM, Doug wrote:
> > 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.
> >
> 
> You should be able to find some good info by googling :"correlated
> subquery"
> 
> 
> 
> Gerry
> ___
> 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

Re: [sqlite] Query suggestion?

[Gerry Snyder]

  On 9/9/2010 11:32 AM, Doug wrote:
> 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.
>

You should be able to find some good info by googling :"correlated subquery"



Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query suggestion?

[Doug]

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

Re: [sqlite] Query suggestion?

[Igor Tandetnik]

Doug  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