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." But it's not the case here. On Sun, Feb 8, 2015 at 6:57 AM, 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 aggregate: sum()". > > > > 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 total > from t group by S_id > union values (NULL, 0)) > select s.S_id, s.total, > (select count(*)+1 from tt as r where r.total > s.total) as rank > from tt as s where S_id is not NULL; > > But my question remains, why is the UNION necessary in the CTE? > > why doesn't this work? ... > > with tt (S_id, total) as > (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total > from t group by S_id) > select s.S_id, s.total, > (select count(*)+1 from tt as r where r.total > s.total) as rank > from tt as s; > > e > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
