CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
JagaSrik
JagaSrik

Reputation: 718

Find the name of the student who has scored highest marks in every sem and each course?

There are 2 tables namely students and markscard, for each i have written the create able script and also some of the sample data in insert statements.

i through i feel some link is missing here and was not able to get the student name who have scored highest marks in every sem and each course, am i wrong or is there something missing? Thanks in advance.

sql script of the table

CREATE TABLE student (
    name     VARCHAR2(100),
    regno    NUMBER(20) PRIMARY KEY,
    dob      DATE,
    course   VARCHAR2(200)
);

INSERT INTO student VALUES (
    'S1',
    1001,
    '5-04-2000',
    'computer'
);

INSERT INTO student VALUES (
    'S2',
    1002,
    '8-04-2010',
    'Electronics'
);

INSERT INTO student VALUES (
    'S3',
    1003,
    '2-04-2100',
    'management'
);

INSERT INTO student VALUES (
    'S4',
    1004,
    '28-05-2000',
    'Electronics'
);

INSERT INTO student VALUES (
    'S5',
    1005,
    '2-04-2000',
    'computer'
);

COMMIT;

CREATE TABLE markscard (
    regno    NUMBER(20)
        REFERENCES student ( regno ),
    sem      VARCHAR2(100),
    sub1     NUMBER(20),
    sub2     NUMBER(20),
    sub3     NUMBER(20),
    tot      NUMBER(20),
    avge     NUMBER(20),
    result   VARCHAR2(200)
);

INSERT INTO markscard VALUES (
    1001,
    1,
    30,
    30,
    30,
    90,
    30,
    'pass'
);

INSERT INTO markscard VALUES (
    1002,
    1,
    10,
    10,
    10,
    100,
    10,
    'fail'
);

INSERT INTO markscard VALUES (
    1003,
    2,
    100,
    100,
    100,
    300,
    100,
    'distinction'
);

INSERT INTO markscard VALUES (
    1004,
    2,
    20,
    20,
    20,
    60,
    20,
    'pass'
);

INSERT INTO markscard VALUES (
    1005,
    1,
    30,
    30,
    30,
    100,
    30,
    'pass'
);

COMMIT;

This is the query i tried but cant able to get student name successfully as i feel there is some link is missing.

SELECT
    MAX(hightest_score),
    course_name,
    sem
FROM
    (
        SELECT
            MAX(markscard.tot) AS hightest_score,
            student.course   AS course_name,
            markscard.sem    AS sem,
            student.name     AS sname
        FROM
            markscard,
            student
        WHERE
            student.regno = markscard.regno
        GROUP BY
            student.course,
            markscard.sem,
            student.name
    )
GROUP BY
    course_name,
    sem;

Upvotes: 2

Views: 1325

Answers (2)

David Blyth
David Blyth

Reputation: 11

I like this option...

WITH RANKED_DATA
     AS (SELECT S.REGNO,
                S.NAME,
                S.COURSE,
                M.SEM,
                M.TOT,
                RANK () OVER (PARTITION BY S.COURSE, M.SEM ORDER BY M.TOT DESC) AS RK
           FROM MARKSCARD M INNER JOIN STUDENT S ON (M.REGNO = S.REGNO))
SELECT *
  FROM RANKED_DATA
 WHERE RK = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

First, learn to use proper, explicit, standard JOIN syntax. Second, the best way to do this uses window functions:

SELECT sm.*
FROM (SELECT s.course as course_name, mc.sem, s.name, mc.tot,
              MAX(tot) OVER (PARTITION BY s.course, mc.sem) as max_tot
      FROM markscard mc JOIN
           student s
           ON s.regno = mc.regno
     ) sm
WHERE tot = max_tot;

Upvotes: 1

Related Questions