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. The outer query is analysed for parameters, but rows have not been fetched at the time inner queries are executed. Your original post said "for computing the median of a group". The fastest way to do that without creating a SQLite extension for it is to first count the number of items in the group then do a SELECT. Unforunately it's going to be faster to do this in your programming language than it is to try to make one convoluted SQL statement to do it all. The counting SELECT would be something like SELECT count(*) FROM myTable WHERE theGroup="redballs" Unfortunately this may give an odd or even result, and the OFFSET parameter must evaluate to an integer, so you cant just halve it, you have to do a little maths or a little 'if'. The median entry would then be the expected SELECT ballSize FROM myTable WHERE theGroup="redballs" ORDER BY ballSize OFFSET [value stored earlier] LIMIT 1 A neater solution would be to define your own aggregate function which finds the median of all the values fed to it. Then you could just do SELECT myMedian(ballSize) FROM myTable WHERE theGroup="redballs" Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
