Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread Richard Hipp
On 2/7/15, Doug Currie doug.cur...@gmail.com wrote: In response to this SO question: http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table I tried to formulate a query without temp tables using an ordinary CTE, but received an error misuse of

Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread Clemens Ladisch
James K. Lowden wrote: 1. Last I checked, SELECT in a column position in the SELECT clause as in select foo (select ...) is not permitted by the SQL standard. This example indeed is not valid SQL syntax. However, SELECT in a column position is allowed: select (select 42); This

Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread R.Smith
On 2/8/2015 10:23 PM, James K. Lowden wrote: I have a couple of efficiency questions for those who know: 1. Is the left-join on a CTE apt to be more effecient than the version that uses a correlated subquery in the SELECT clause? I'm guessing it matters in some DBs but from testing it

Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread Keith Medcalf
-users- boun...@sqlite.org] On Behalf Of R.Smith Sent: Monday, 9 February, 2015 04:51 To: sqlite-users@sqlite.org Subject: Re: [sqlite] ordinary CTE containing sum() On 2/8/2015 10:23 PM, James K. Lowden wrote: I have a couple of efficiency questions for those who know: 1. Is the left-join

Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread Doug Currie
: Monday, 9 February, 2015 04:51 To: sqlite-users@sqlite.org Subject: Re: [sqlite] ordinary CTE containing sum() On 2/8/2015 10:23 PM, James K. Lowden wrote: I have a couple of efficiency questions for those who know: 1. Is the left-join on a CTE apt to be more effecient than the version

Re: [sqlite] ordinary CTE containing sum()

2015-02-09 Thread Doug Currie
Thank you, Richard. It works for me now. e On Mon, Feb 9, 2015 at 1:30 PM, Richard Hipp d...@sqlite.org wrote: On 2/7/15, Doug Currie doug.cur...@gmail.com wrote: In response to this SO question:

Re: [sqlite] ordinary CTE containing sum()

2015-02-08 Thread Darko Volaric
I'm interested in this too (since I rely on it working). According to the documentation this should be perfectly legal and seems like an arbitrary limitation (or a bug). It says: An ordinary common table expression works as if it were a view that exists for the duration of a single statement.

Re: [sqlite] ordinary CTE containing sum()

2015-02-08 Thread Doug Currie
In response to this SO question: http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table I tried to formulate a query without temp tables using an ordinary CTE, but received an error misuse of aggregate: sum(). tonypdmtr

Re: [sqlite] ordinary CTE containing sum()

2015-02-08 Thread James K. Lowden
On Sun, 8 Feb 2015 09:57:54 -0500 Doug Currie doug.cur...@gmail.com wrote: tonypdmtr http://stackoverflow.com/users/3735873/tonypdmtr on SO posted a CTE solution; it is something like this, which works for me: with tt (S_id, total) as (select S_id, sum(ca1) + sum(ca2) + sum(exam) as

[sqlite] ordinary CTE containing sum()

2015-02-07 Thread Doug Currie
In response to this SO question: http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table I tried to formulate a query without temp tables using an ordinary CTE, but received an error misuse of aggregate: sum(). This works: sqlite with tt (S_id,

Re: [sqlite] ordinary CTE containing sum()

2015-02-07 Thread James K. Lowden
On Sat, 7 Feb 2015 12:31:37 -0500 Doug Currie doug.cur...@gmail.com wrote: In response to this SO question: http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table I tried to formulate a query without temp tables using an ordinary CTE, but