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 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; I don't know the answer to your questions, except to say that IMO the SO answer is incorrect for at least two reasons: 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. 2. In principle, adding data in a query to "make it work" is the wrong approach. Here "and S_id is not NULL" is needed to compensate for that UNION. Why it's "needed", in the sense that you get "misuse of aggregate", I don't know. It looks like valid SQLite syntax to me. But I like my corrected syntax better: with tt (S_id, total) as (select S_id, sum(ca1 +ca2 + exam) as total from t group by S_id) select S.S_id, S.total, 1+count(lesser.total) as RANK from tt as S left join tt as lesser on S.total < lesser.total group by S.S_id, S.total order by S.total desc; S_id total RANK ---------- ---------- ---------- 2 198 1 4 198 1 5 183 3 3 165 4 1 143 5 because it produces the desired result using standard syntax. I would argue that's a clearer expression of the problem, too. 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? 2. Is there any performance difference between sum(ca1 +ca2 + exam) and sum(ca1) + sum(ca2) + sum(exam) I would expect the left join is faster than a correlated subquery, and that fewer aggregates is better than more. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
