Geek Answers Handbook

GROUP BY LIMITATIONS

Disclaimer . I am SQL newb and this is for the class, but I can really use the notch in the right direction.

I have three tables:

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

I am trying to write an Oracle compatible SQL query that returns a table with student name ( student.sname) with the highest gpa value in each section (including section.cnoand section.sectno), as well as all other attributes from section.

I managed to use the aggregated query and GROUP BYto 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

section, , (student.sname). SELECT, GROUP BY, . WHERE FROM , , .

, , !

+5
6

, Oracle 9i +, GPA ( ), :

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

CTE:

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

, GPA, :

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
+3

: , GPA . .

+1

, :

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 


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
0

: -

  • Group By
  • GPA cno sectno.
  • Max GPA cno sectno.
  • , , , . . INNER JOIN
  • , GPA 1 ,

, !

0

, . . Oracle RANK() , GPA .

:

(student.sname) gpa (section.cno section.sectno), .

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 links, change RANK () to ROW_NUMBER ()

0
source

Although this is answered, but still I want to provide a beautiful explanation , it is very useful for beginners. Also, Introduction to SQL has such rules.

0
source

More articles:


All Articles