By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 |
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | 0 |
CREATE TABLE Student (
student_id NUMBER,
student_name VARCHAR2(50)
);
CREATE TABLE Subject (
subject_id NUMBER,
student_id NUMBER,
subject_name VARCHAR2(50),
marks NUMBER
);
INSERT ALL
INTO Student (student_id, student_name) VALUES (1001, 'Sanjay Naik')
INTO Student (student_id, student_name) VALUES (1002, 'Rohit Sharma')
INTO Student (student_id, student_name) VALUES (1003, 'Virat Kolhi')
INTO Student (student_id, student_name) VALUES (1004, 'Nitish Kumar Reddy')
SELECT 1 FROM DUAL;
4 rows affected
INSERT ALL
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (1, 1001, 'Maths', 72)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (2, 1001, 'English', 84)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (1, 1002, 'Maths', 79)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (2, 1002, 'English', 94)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (1, 1003, 'Maths', 65)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (2, 1003, 'English', 89)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (1, 1004, 'Maths', 54)
INTO Subject (subject_id, student_id, subject_name, marks) VALUES (2, 1004, 'English', 51)
SELECT 1 FROM DUAL;
8 rows affected
WITH ranked_students AS (
SELECT
su.subject_name,
st.student_name,
su.marks,
RANK() OVER (PARTITION BY su.subject_name ORDER BY su.marks DESC) AS rnk
FROM
Subject su
JOIN
Student st ON su.student_id = st.student_id
)
SELECT
subject_name,
student_name,
marks
FROM
ranked_students
WHERE
rnk <= 3
ORDER BY
subject_name, rnk;
| SUBJECT_NAME | STUDENT_NAME | MARKS |
|---|---|---|
| English | Rohit Sharma | 94 |
| English | Virat Kolhi | 89 |
| English | Sanjay Naik | 84 |
| Maths | Rohit Sharma | 79 |
| Maths | Sanjay Naik | 72 |
| Maths | Virat Kolhi | 65 |