Limitations of GROUP BY

Question

Disclaimer: I'm an SQL newb and this is for a class, but I could really use a poke in the right direction.

I've got these three tables:

student(_sid_, sname, sex, age, year, gpa)
section(_dname_, _cno_, _sectno_, pname)
enroll(_sid_, grade, _dname_, _cno_, _sectno_)
(primary keys denoted by underscores)

I'm trying to write an Oracle-compatible SQL query that returns a table with the student's name (student.sname) that has the highest gpa in each section (that's including section.cno and section.sectno) as well as all the other attributes from section.

I've managed to use an aggregate query and GROUP BY to get the maximum GPA for each section:

  SELECT MAX(s.gpa), e.cno, e.sectno  
    FROM enroll e, 
         student s  
   WHERE s.sid = e.sid  
GROUP BY e.cno, e.sectno

Let alone the other section attributes, I can't even figure out how to tack on the student name (student.sname). If I add it to the SELECT clause, it has to be included in GROUP BY which messes up the rest of the query. If I use this entire query inside the WHERE or FROM clause of an outer query, I can only access the three fields in the table, which isn't that much use.

I know you can't give me the exact answer, but any hints would be appreciated!

Answer 1

Hint: consider that there might be more than one student with the highest GPA in a class. An outer query only needs the three fields.

Answer 2

Maybe shortest:

SELECT DISTINCT e.cno, e.sectno , e...,
       FIRST_VALUE(s.sname) OVER 
                              (PARTITION BY e.cno, e.sectno ORDER BY s.gpa DESC)
FROM enroll e, 
     student s  
WHERE s.sid = e.sid 

Or

SELECT A.* 
FROM
    (  SELECT s._sid, s.sname, e.cno, e.sectno  ,..., s.gpa
              MAX(s.gpa) OVER (PARTITION BY e.cno, e.sectno) AS maxgpa
       FROM enroll e, 
            student s  
       WHERE s.sid = e.sid  
    ) A
WHERE A.maxgpa = A.gpa

Answer 3

Here are some pointers :-

1. You are on the right track with your Group By query
2. This returns you the max GPA for each section based on the cno and sectno fields
3. Now, you have the Max GPA value for each cno and sectno combination.
4. Use this data that you have in like a reverse manner if u like to now find all the students matching these combination values. HINT : Consider the results of your Group By query as a table and Use a INNER JOIN
5. Even If it is possible that there is more than 1 students for the same max GPA, you will still get them all

Hope this helps!!

Answer 4

Assuming Oracle 9i+, to get only one of the students with the highest GPA (in the event of ties) use:

WITH summary AS (
   SELECT e.*,
          s.name,
          ROW_NUMBER() OVER(PARTITION BY e.cno, e.sectno
                                ORDER BY s.gpa DESC) AS rank
     FROM ENROLL e
     JOIN STUDENT s ON s.sid = e.sid)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1

Non CTE equivalent:

SELECT s.*
  FROM (SELECT e.*,
               s.name,
               ROW_NUMBER() OVER(PARTITION BY e.cno, e.sectno
                                     ORDER BY s.gpa DESC) AS rank
          FROM ENROLL e
          JOIN STUDENT s ON s.sid = e.sid) s
 WHERE s.rank = 1

If you want to see all students who tied for GPA, use:

WITH summary AS (
   SELECT e.*,
          s.name,
          DENSE_RANK OVER(PARTITION BY e.cno, e.sectno
                              ORDER BY s.gpa DESC) AS rank
     FROM ENROLL e
     JOIN STUDENT s ON s.sid = e.sid)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1

Answer 5

This should give you what you are looking for. See Oracle's RANK() function for details on how the GPAs are ranked from highest to lowest by section.

Requirements:

Return a table with the student's name (student.sname) that has the highest gpa in each section (section.cno and section.sectno) as well as all the other attributes from section.

SELECT * FROM 
(
    SELECT 
        s.sname,
        s.gpa,
        sec.dname, 
        sec.cno, 
        sec.sectno,
        sec.pname,
        /* for each "sec.cno, sec.sectno", this will rank each GPA in order from highest to lowest. Ties will have the same rank. */
        RANK() OVER(PARTITION BY sec.cno, sec.sectno ORDER BY s.gpa DESC) as r_rank
    FROM
        enroll e, 
        student s,
        section sec
    WHERE 
        /* join enroll with student */
        s.sid = e.sid
        /* join section with enroll */
        AND sec.dname = e.dname
        AND sec.cno = e.cno
        AND sec.sectno = e.sectno
)
WHERE r_rank = 1  /* this returns only the highest GPA (maybe multiple students) for each "sec.cno, sec.sectno" combination */ 
;

Note: If you do not want ties, change RANK() to ROW_NUMBER()