I have some problems in solving a SQL query.
There are two tables table1 and table2.
table1 has studentId (Primary Key) and name
table2 has studentId(FK), subject, marks
I am trying to write a query that will print subject wise the top 3 scorers along with the marks that they scored.
In Oracle11g I can easily do it with the Partition function. How to do the same in MySQL?
Edit: I have created the table and input the values:
CREATE TABLE table1
(
rollno int NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(60) NOT NULL
);
CREATE TABLE table2
(
rollno int not null,
subject varchar(60) not null,
marks int not null,
foreign key (rollno) references table1(rollno)
);
insert into table1 values(32,"john");
insert into table1 values(33,"amber");
insert into table1 values(34,"meher");
insert into table1 values(35,"lucy");
insert into table1 values(36,"philips");`
insert into table2 values(32,"maths",82);
insert into table2 values(32,"english",28);
insert into table2 values(32,"science",46);
insert into table2 values(33,"maths",67);
insert into table2 values(33,"english",89);
insert into table2 values(33,"science",72);
insert into table2 values(34,"maths",83);
insert into table2 values(34,"english",65);
insert into table2 values(34,"science",78);
insert into table2 values(35,"maths",98);
insert into table2 values(35,"english",87);
insert into table2 values(35,"science",81);
insert into table2 values(36,"maths",56);
insert into table2 values(36,"english",67);
insert into table2 values(36,"science",43);`
I am using the below code:
select *
from
( Select t1.name, t2.subject as su, t2.marks
from table1 t1
inner join table2 t2 on t1.rollno=t2.rollno
order by t2.marks
)t
order by su, marks desc;
But this code prints all students list subject wise . I am facing problem in limiting into top 3
