When using LIMIT in a subquery it seems the expression cannot access the outer query's tables. Other complex expressions are supported in LIMIT, so I was curious if this is a defined limitation or something else?
Example: sqlite> create table test (f int); sqlite> insert into test values (1); sqlite> insert into test values (2); sqlite> insert into test values (3); sqlite> insert into test values (4); sqlite> insert into test values (5); sqlite> select avg(f) from test; 3.0 sqlite> select * from test limit (select avg(f) from test); 1 2 3 sqlite> select *, (select count(*) from test t1 limit t2.f) from test t2; Error: no such column: t2.f sqlite> select *, (select count(*) from test t1 where t1.f <= t2.f) from test t2; 1|1 2|2 3|3 4|4 5|5 sqlite> So the average is intentionally a value that losslessly converts to an integer. I can use a subquery inside the LIMIT clause without issue. However, when I use limit inside a subquery that correlates to the outer query, I get no such column. A similar subquery with WHERE instead of LIMIT works fine. Thanks, Sam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
