Re: [sqlite] Using "sub-select" to return limit

[Don V Nielsen]

Thanks.  I was afraid of that.  I got out my old Data Base Systems books
and was trying to figure out what I forgot.  Turns out I didn't forget it.
 It's just not possible.

Thanks again.  Off to coding.

On Thu, Nov 3, 2011 at 9:08 PM, Pavel Ivanov  wrote:

> > What I ultimately want to do is iterate through a table that contains
> the> limit and use that value to select all matching values from another
> table,> limiting the number of records selected from the group to that
> defined by> limit.
> There's no way to do such thing using only SQL. You have to select
> data from your table of limits into your programming language, iterate
> through results and for each row issue separate select statement with
> appropriate limit (which will be a constant not a nested select
> query).
>
>
> Pavel
>
>
> On Thu, Nov 3, 2011 at 5:23 PM, Don V Nielsen 
> wrote:
> > Given the following, I get an error that f.zip does not exist.
>  Obviously,
> > I am mentally missing something contextually, but I'm not getting it.
> >  Would someone work through the scope of things in this select.
> >
> > select p.*,pr.rowid from pool_wi as p
> > inner join add_priorities as pr on pr.prty = p.prty
> > where p.zip = '53005' and p.crrt = 'C022'
> > order by pr.rowid
> > limit (
> >  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
> > = p.crrt
> > )
> >
> > What I ultimately want to do is iterate through a table that contains the
> > limit and use that value to select all matching values from another
> table,
> > limiting the number of records selected from the group to that defined by
> > limit.  For example, a table would hold two rows of zip, route and limit,
> > [53005,C020,1] & [53005,C022,2].  I want to use the zip and route to
> select
> > all matching records from another table, but limit the result of the
> > sub-select to the qty of records as defined by limit.
> >
> > Thanks for your time and consideration.
> > dvn
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using "sub-select" to return limit

[Pavel Ivanov]

> What I ultimately want to do is iterate through a table that contains the> 
> limit and use that value to select all matching values from another table,> 
> limiting the number of records selected from the group to that defined by> 
> limit.
There's no way to do such thing using only SQL. You have to select
data from your table of limits into your programming language, iterate
through results and for each row issue separate select statement with
appropriate limit (which will be a constant not a nested select
query).


Pavel


On Thu, Nov 3, 2011 at 5:23 PM, Don V Nielsen  wrote:
> Given the following, I get an error that f.zip does not exist.  Obviously,
> I am mentally missing something contextually, but I'm not getting it.
>  Would someone work through the scope of things in this select.
>
> select p.*,pr.rowid from pool_wi as p
> inner join add_priorities as pr on pr.prty = p.prty
> where p.zip = '53005' and p.crrt = 'C022'
> order by pr.rowid
> limit (
>  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
> = p.crrt
> )
>
> What I ultimately want to do is iterate through a table that contains the
> limit and use that value to select all matching values from another table,
> limiting the number of records selected from the group to that defined by
> limit.  For example, a table would hold two rows of zip, route and limit,
> [53005,C020,1] & [53005,C022,2].  I want to use the zip and route to select
> all matching records from another table, but limit the result of the
> sub-select to the qty of records as defined by limit.
>
> Thanks for your time and consideration.
> dvn
> ___
> 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

[sqlite] Using "sub-select" to return limit

[Don V Nielsen]

Given the following, I get an error that f.zip does not exist.  Obviously,
I am mentally missing something contextually, but I'm not getting it.
 Would someone work through the scope of things in this select.

select p.*,pr.rowid from pool_wi as p
inner join add_priorities as pr on pr.prty = p.prty
where p.zip = '53005' and p.crrt = 'C022'
order by pr.rowid
limit (
  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
= p.crrt
)

What I ultimately want to do is iterate through a table that contains the
limit and use that value to select all matching values from another table,
limiting the number of records selected from the group to that defined by
limit.  For example, a table would hold two rows of zip, route and limit,
[53005,C020,1] & [53005,C022,2].  I want to use the zip and route to select
all matching records from another table, but limit the result of the
sub-select to the qty of records as defined by limit.

Thanks for your time and consideration.
dvn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users