title: To get top three names of students with marks in each subject from 2 tables in oracle db from 2 tables
body: <p>Suppose I have two tables: <br> <strong>1)</strong> Student with name and roll number<br> <strong>2)</strong> Table with roll number, subject and marks.</p>
  
  <p>I want to get the output such that it will show me the top 3 students in each subject with names.</p>
  
  <p>Script of table which can be used is:</p>
  
  <pre><code>CREATE TABLE STUDENT ( ROLL_NO INT PRIMARY KEY, NAME VARCHAR(20));
  INSERT INTO STUDENT VALUES (1,'NIKHIL');
  INSERT INTO STUDENT VALUES (2,'VARUN');
  INSERT INTO STUDENT VALUES (3,'NISHANT');
  INSERT INTO STUDENT VALUES (4,'VISHAL');
  INSERT INTO STUDENT VALUES (5,'GOURAV');
  INSERT INTO STUDENT VALUES (6,'HEMANT');
  INSERT INTO STUDENT VALUES (7,'SHUBHAM');
  INSERT INTO STUDENT VALUES (8,'DHAWAN');
  INSERT INTO STUDENT VALUES (9,'GAUTAM');
  
  
  CREATE TABLE MARKS ( ROLL_NO INT, SUBJECT VARCHAR(20), MARKS INT);
  INSERT INTO MARKS VALUES('1','MATHS','92');
  INSERT INTO MARKS VALUES('1','SCIENCE','80');
  INSERT INTO MARKS VALUES('1','ENGLISH','98');
  INSERT INTO MARKS VALUES('1','HINDI','81');
  INSERT INTO MARKS VALUES('2','MATHS','89');
  INSERT INTO MARKS VALUES('2','SCIENCE','100');
  INSERT INTO MARKS VALUES('2','ENGLISH','81');
  INSERT INTO MARKS VALUES('2','HINDI','82');
  INSERT INTO MARKS VALUES('3','MATHS','98');
  INSERT INTO MARKS VALUES('3','SCIENCE','92');
  INSERT INTO MARKS VALUES('3','ENGLISH','88');
  INSERT INTO MARKS VALUES('3','HINDI','83');
  INSERT INTO MARKS VALUES('4','MATHS','88');
  INSERT INTO MARKS VALUES('4','SCIENCE','82');
  INSERT INTO MARKS VALUES('4','ENGLISH','85');
  INSERT INTO MARKS VALUES('4','HINDI','97');
  INSERT INTO MARKS VALUES('5','MATHS','94');
  INSERT INTO MARKS VALUES('5','SCIENCE','90');
  INSERT INTO MARKS VALUES('5','ENGLISH','97');
  INSERT INTO MARKS VALUES('5','HINDI','89');
  INSERT INTO MARKS VALUES('6','MATHS','89');
  INSERT INTO MARKS VALUES('6','SCIENCE','82');
  INSERT INTO MARKS VALUES('6','ENGLISH','84');
  INSERT INTO MARKS VALUES('6','HINDI','85');
  INSERT INTO MARKS VALUES('7','MATHS','82');
  INSERT INTO MARKS VALUES('7','SCIENCE','100');
  INSERT INTO MARKS VALUES('7','ENGLISH','92');
  INSERT INTO MARKS VALUES('7','HINDI','98');
  INSERT INTO MARKS VALUES('8','MATHS','99');
  INSERT INTO MARKS VALUES('8','SCIENCE','91');
  INSERT INTO MARKS VALUES('8','ENGLISH','86');
  INSERT INTO MARKS VALUES('8','HINDI','82');
  INSERT INTO MARKS VALUES('9','MATHS','100');
  INSERT INTO MARKS VALUES('9','SCIENCE','95');
  INSERT INTO MARKS VALUES('9','ENGLISH','81');
  INSERT INTO MARKS VALUES('9','HINDI','81');
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!4/e97bb4" rel="nofollow noreferrer"><strong>Run It Here</strong></a></p>
  
  <p>Thanks in advance for helping
  I need to run it on oracle</p>