Simon Slavin wrote:
> On 11 Apr 2013, at 2:38pm, Clemens Ladisch <[email protected]> wrote:
>>> sqlite> create table t(x);
>>> sqlite> select (select 42 limit 1 offset (select t.x)) from t;
>>> Error: no such column: t.x
>
>> It should come (or be derived) from the current row in the outer query.
>
> Sorry, but inner queries are performed first.
Sorry, but *correlated* inner queries are performed once for each record
in the outer query.
To take your ballsy example: with the following data:
create table balls(
color varchar(10),
size integer
);
insert into balls values('blue', 1);
insert into balls values('blue', 2);
insert into balls values('red', 3);
insert into balls values('red', 44);
insert into balls values('red', 555);
this query works just fine:
select color,
(select avg(size) from balls where color = b.color)
from (select distinct color from balls) b;
(This could be done much easier with GROUP BY; I'm just demonstrating
how correlated subqueries work.)
> Your original post said "for computing the median of a group".
With PostgreSQL, this works (<http://sqlfiddle.com/#!12/50d20/6>):
select color,
(select size
from balls
where color = b.color
order by size
limit 1
offset (select cast(count(*) / 2 as integer)
from balls
where color = b.color)
) as median
from (select distinct color from balls) b;
> The fastest way to do that without creating a SQLite extension ...
Thanks for the workaround, but I'm asking if there is any good reason
why SQLite does not support this. As far as I can tell, (scalar)
correlated subqueries work in every other context.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
