Getting top(N) in database by group, where n is read from database in sqlite

manpreet Best Answer 3 years ago

I am looking to solve a variant of the "select top(N) by group" question, but my problem is that N has to be looked up in the database. I have a way of doing it, but am hoping to find a clearer way.

If it helps, I am using sqlite3.

I am designing a course, and have a table that assigns the weights to be stored in the table gradeSyllabus:

title       weight      dropLowest
----------  ----------  ----------
Homework    0.6         0         
Test        0.4         1

i.e. Homeworks make up 60% of the grade with none dropped, while tests make up 40% of the grade with the lowest test dropped.

Here is some fake student data from the table gradeGrades:

studentId   assess_title  assess_num  score     
----------  ------------  ----------  ----------
john345     Homework      1           75.0      
john345     Homework      2           85.0      
john345     Homework      3           64.0      
john345     Test          1           75.0      
john345     Test          2           85.0      
john345     Test          3           64.0      
mary111     Test          1           78.0      
mary111     Test          2           72.0      
mary111     Test          3           84.0      
mary111     Homework      1           90.0      
mary111     Homework      2           92.0      
mary111     Homework      3           88.0

I can find the average for each student easily enough:

SELECT GG.studentId, assess_title, avg(score) as overall, weight
   FROM gradeGrades GG INNER JOIN gradeSyllabus GS
   ON (assess_title = title)
   GROUP BY GG.studentId, assess_title;

This gives the following results:

studentId   assess_title  overall           weight    
----------  ------------  ----------------  ----------
john345     Homework      74.6666666666667  0.6       <-- correct
john345     Test          74.6666666666667  0.4       
mary111     Homework      90.0              0.6       <-- correct
mary111     Test          78.0              0.4 

The tests need the lowest test dropped. In general, I cannot use min (because we may change the syllabus to drop the lowest 2 tests). Here is a query that addresses this problem:

SELECT studentId, assess_title, sum(score) / count(*) as overall
   FROM gradegrades G1
        WHERE (select count(*) from gradegrades as G2
                   WHERE G1.studentid = G2.studentId AND
                         G1.assess_title = G2.assess_title AND
                         G2.score <= G1.score) -- select lowest
                         > (SELECT dropLowest FROM gradeSyllabus
                            WHERE gradeSyllabus.title = G1.assess_title)
   GROUP BY studentId, assess_title;

This produces:

studentId   assess_title  overall         
----------  ------------  ----------------
john345     Homework      74.6666666666667  
john345     Test          80.0            
mary111     Homework      90.0            
mary111     Test          81.0  

where all the overall columns are correct.

My questions:

1) Can I get the weight from the gradeSyllabus table from the subselect? Or do I need another join?

2) Is there a better (i.e. clearer) way of generating this table in the first place? Efficiency would be nice, but at the moment my tables are rather small (~ 30 students)

3) Are there (obvious?) problems with the query?