Re: [sqlite] aggregate and index.

[trash Spam]

2012/1/10 Richard Hipp 

> On Tue, Jan 10, 2012 at 12:56 PM, trash Spam 
> wrote:
>
> SELECT (SELECT min(col3) FROM t1)+1;
>

Works, thank you.


> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] aggregate and index.

[Richard Hipp]

On Tue, Jan 10, 2012 at 12:56 PM, trash Spam  wrote:

> Hi,
>
> Simplified example :
> Table t1
> (
> col1number  primary key
>,col2varchar( 50 )
>,col3number
> )
>
> select
>min( col3 )
> fro
>t1
> ;
> explain plan :
> orderfromdetail
> 00TABLE t1 WITH INDEX idx_t1_col3 ORDER BY
>
> select
>min( col3 ) + 1
> fro
>t1
> ;
> orderfromdetail
> 00TABLE t1
>
> Why the second case cannot use index idx_t1_col3 ?
>
> How i can force the query to use the index ?
>


SELECT (SELECT min(col3) FROM t1)+1;


>
> In the real case the query is :
> select
>col2
> from
>t1
> where
>   col3 = (
>  select
>  min( col3 ) + :delta
>  from
> t1
>  )
> ;
> I don't know the explain plan with bind variable.
>
> Thanks.
>
> --
> Philippe.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users