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 received an error "misuse of aggregate: sum()". My standard answer is http://www.schemamania.org/sql/#rank.rows. You don't need a CTE; it's just a syntactic convenience. Substituting his table in my example, something like this should work: select S.id, S.total, count(lesser.total) as RANK from ( select id, sum(cal1 + cal2 + exam) as total from T group by id ) as S join ( select id, sum(cal1 + cal2 + exam) as total from T group by id ) as as lesser on S.SID >= lesser.SID group by S.SID, S.total order by S.SID; Someone will doubtless be tempted to suggest that's inefficient in SQLite, and that may be so. (I haven't checked.) It needed be, though. The system could detect the repeated subquery and evaluate it once. Not that it matters for any dataset of students and exams on the planet! :-) --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
