Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 18:06 heeft Simon Slavin het volgende geschreven: On 21 Jul 2013, at 4:41pm, E.Pasma wrote: Is a change in SQLite imaginable such that column expressions are not re-evaluated with each reference to the column alias? ... ... This is partly because

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On Jul 21, 2013, at 7:47 PM, Igor Tandetnik wrote: > This query is in fact perfectly legal. It's OK to refer to column aliases in > ORDER BY clause. Perhaps in SQLite, yes. select 1 as a order by 1; select 1 as a order by a; select x as a from ( select 1 as x ) order by

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/21/2013 12:06 PM, Simon Slavin wrote: One of the problems with this is that it's not standard SQL. You're not meant to be able to refer to column aliases inside the SELECT that defines them. For instance SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members is not

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 21-07-2013 12:01, E.Pasma wrote: Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven: Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

You can also use this form: select id, a, b, 1.0 * a / b as c from ( select id, (select sum(v) from ot as ot1 where ot1.v > categories.id) as a, (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b from categories order by +id ) order by c; > -Original Message- > From:

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 21 Jul 2013, at 4:41pm, E.Pasma wrote: > Is a change in SQLite imaginable such that column expressions are not > re-evaluated with each reference to the column alias? > This could also improve queries that use aliases only in the order by clause, > like > select id,

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven: RSmith wrote: On 2013/07/21 12:01, E.Pasma wrote: Only the execution plan of this query is not optimal: 0|0|0|SCAN TABLE categories (~100 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE ot AS ot1

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

RSmith wrote: > On 2013/07/21 12:01, E.Pasma wrote: >> Only the execution plan of this query is not optimal: >> 0|0|0|SCAN TABLE categories (~100 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 >> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 >>

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Well yes, the plan does not read like one would expect an optimal plan to read like - but to the purpose of the original request there is no more-optimal a plan, is there?. The entire column used to sort by is made up on the spot and therefore temp BTrees are needed and all the other quirks, as

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven: Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories (id number); insert into

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories (id number); insert into categories (id) values (5),(10),(15); create table ot (v number); insert into

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 7:04 PM, Mikael wrote: C is not a present column, but is the result of an expression involving A and B (namely, the expression A / B). Yes. And this is notable because...? Can you please take this into consideration and update the query you proposed? :)) In what way do you

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Igor, C is not a present column, but is the result of an expression involving A and B (namely, the expression A / B). Can you please take this into consideration and update the query you proposed? :)) Thanks, Mikael 2013/7/20 Igor Tandetnik > On 7/20/2013 9:54 AM,

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 3:29 PM, E.Pasma wrote: Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven: select id, a, b, a/b as c from ( SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven: On 7/20/2013 9:54 AM, Mikael wrote: So again, SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses categories.id as input])

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Is this some mental exercise? Why can't you do this in the calling code rather than some funky SQL select? Or add a custom function? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mikael Sent: Saturday, July 20, 2013 8:54 AM

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 9:54 AM, Mikael wrote: So again, SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses categories.id as input]) AS b, a / b AS c FROM categories ORDER BY c; select id, a, b, a/b as c from

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Ah I realize now I didn't write it out in the example, but by thing and thing2 I just allegorically wanted to represent a *very complex* subselect, so here we go more clearly: Inlining this subselect's SQL expression in the "A / B" part would make it need to execute once more, which would make it

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 20-07-2013 15:26, Mikael wrote: SELECT id, (SELECT thing FROM othertable WHERE othertable.something = categories.id) AS a, (SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id) AS b, a / b AS c FROM categories ORDER BY c; Is there any way whatsoever to do this?

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Clemens! Hmm. Let's see how this fits into the bigger picture: I have a table "categories". I'm doing a select for each of its id:s i.e: SELECT id FROM categories; Now, what I want to do is that I want to do two separate subselects that use categories.id for the respective row the

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Mikael wrote: > Is anything like "select 7 as a, 8 as b, a / b as c;" possible? Not directy, but you could use a subquery: SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi! Is anything like "select 7 as a, 8 as b, a / b as c;" possible? I.e., I select one value into one column and another into another (it's a subselect, expression etc.), and then I want to generate a separate column that's an expression involving both the earlier two generated values. Possibly