Display only max result from SQL query in oracle

Question

I have a query:

SELECT 
STUDENT.S_ID AS "ID", 
STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name",
COUNT(COURSE.COURSE_NO) AS "Number of courses", 
SUM(COURSE.CREDITS) AS "Total Credits"
FROM 
STUDENT
JOIN ENROLLMENT 
ON ENROLLMENT.S_ID = STUDENT.S_ID
JOIN COURSE_SECTION
ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID
JOIN COURSE
ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO
GROUP BY STUDENT.S_ID, STUDENT.S_lAST, STUDENT.S_FIRST;

It displays the result:

ID     Student Name                    Number of courses Total Credits
------ ------------------------------- ----------------- -------------
JO100  Jones Tammy                                     6            21
MA100  Marsh John                                      5            15
SM100  Smith Mike                                      2             6
PE100  Perez Jorge                                     6            18
JO101  Johnson Lisa                                    5            15
NG100  Nguyen Ni                                       4            12

I want only the student(s) with the highest Total credits. For this it would be student with ID JO100. How do achieve this in the query?

Please provide the Table structure, input data and sample expected output — shrek
– shrek, Commented May 14, 2018 at 22:23
If you need to return only top row then have you tried using SELECT MAX(total_credits)...? This will return you JO100. — Art
– Art, Commented May 15, 2018 at 13:34

shrek · Accepted Answer · 2018-05-14 22:57:10Z

1

Try this -

SELECT
    *
FROM
    (
        SELECT
            A.*,
            DENSE_RANK() OVER(
                ORDER BY "TOTAL CREDITS" DESC
            ) AS RNK
        FROM
            (
                SELECT
                    STUDENT.S_ID AS "ID",
                    STUDENT.S_LAST
                     || ' '
                     || STUDENT.S_FIRST AS "STUDENT NAME",
                    COUNT(COURSE.COURSE_NO) AS "NUMBER OF COURSES",
                    SUM(COURSE.CREDITS) AS "TOTAL CREDITS"
                FROM
                    STUDENT
                    JOIN ENROLLMENT ON ENROLLMENT.S_ID   = STUDENT.S_ID
                    JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID   = ENROLLMENT.C_SEC_ID
                    JOIN COURSE ON COURSE.COURSE_NO   = COURSE_SECTION.COURSE_NO
                GROUP BY
                    STUDENT.S_ID,
                    STUDENT.S_LAST,
                    STUDENT.S_FIRST
            ) A
    )
WHERE
    RNK   = 1;

edited May 14, 2018 at 22:57

answered May 14, 2018 at 22:33

shrek

8876 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RoMEoMusTDiE Over a year ago

does the derived table in oracle not require alias?

Brandon Tupiti Over a year ago

shrek but what if two students have the same total credits?

Brandon Tupiti Over a year ago

Following your query, if two students have the same value it would still bring back the first row, which is incorrect.

Tolu · Accepted Answer · 2018-05-14 22:33:50Z

0

;With CTE as
(
SELECT 
STUDENT.S_ID AS "ID", 
STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name",
COUNT(COURSE.COURSE_NO) AS "Number of courses", 
SUM(COURSE.CREDITS) AS "Total Credits"
FROM 
STUDENT
JOIN ENROLLMENT 
ON ENROLLMENT.S_ID = STUDENT.S_ID
JOIN COURSE_SECTION
ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID
JOIN COURSE
ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO
GROUP BY STUDENT.S_ID, STUDENT.S_lAST, STUDENT.S_FIRST
)
Select Id, "Student Name", "Number of Courses", "Total Credits"
From CTE
Where "Total Credits" = (Select Max("Total Credits") From Cte);

answered May 14, 2018 at 22:33

Tolu

1754 silver badges10 bronze badges

3 Comments

Brandon Tupiti Over a year ago

What does CTE mean?

Tolu Over a year ago

Common Table expression. see wiki and stackoverflow question. I created one, and called it CTE, could just as well have called it anything.

Tolu Over a year ago

@RyanThompson, I am curious to know if you had any issues with my answer.

Collectives™ on Stack Overflow

Display only max result from SQL query in oracle

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related