> > > 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
