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, total) as ...> (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total ...> from t group by S_id) ...> select * from tt ; 1|143 2|198 3|165 4|198 5|183 but with the same CTE this fails, even though the select statement after the CTE works with an equivalent temporary table: sqlite> 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; Error: misuse of aggregate: sum() Any suggestions? Thanks. -- e _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
