title: Limitations of GROUP BY
body: <p><strong>Disclaimer</strong>: I'm an SQL newb and this is for a class, but I could really use a poke in the right direction.</p>
  
  <p>I've got these three tables:  </p>
  
  <p><code>student(_sid_, sname, sex, age, year, gpa)</code><br>
  <code>section(_dname_, _cno_, _sectno_, pname)</code><br>
  <code>enroll(_sid_, grade, _dname_, _cno_, _sectno_)</code><br>
  (primary keys denoted by underscores)</p>
  
  <p>I'm trying to write an Oracle-compatible SQL query that returns a table with the student's name (<code>student.sname</code>) that has the highest gpa in each section (that's including <code>section.cno</code> and <code>section.sectno</code>) as well as all the other attributes from <code>section</code>.</p>
  
  <p>I've managed to use an aggregate query and <code>GROUP BY</code> to get the maximum GPA for each section:</p>
  
  <pre><code>  SELECT MAX(s.gpa), e.cno, e.sectno  
      FROM enroll e, 
           student s  
     WHERE s.sid = e.sid  
  GROUP BY e.cno, e.sectno
  </code></pre>
  
  <p>Let alone the other <code>section</code> attributes, I can't even figure out how to tack on the student name (<code>student.sname</code>).  If I add it to the <code>SELECT</code> clause, it has to be included in <code>GROUP BY</code> which messes up the rest of the query.  If I use this entire query inside the <code>WHERE</code> or <code>FROM</code> clause of an outer query, I can only access the three fields in the table, which isn't that much use.</p>
  
  <p>I know you can't give me the exact answer, but any hints would be appreciated!</p>