I have a table in which student marks in each subject and i have to get query in such a way that i will able to get all top 5 student in every subject who secure highest marks.
Here is a sample table:
My expected output look somthing like :
Top five student in PCM, ART, PCB on the basis of students marks,And also if two or more student secure same than those record also need to be in list with single query.
Technically, what you want to accomplish is not possible using a single SQL query. Had you only wanted one student per subject you could have achieved that using GROUP BY, but in your case it won't work.
The only way I can think of to get 5 students for each subject would be to write x queries, one for each subject and use UNION to glue them together. Such query will return a maximum of 5x rows.
Since you want to get the top 5 students based on the mark, you will have to use an ORDER BY clause, which, in combination with the UNION clauses will cause an error. To avoid that, you will have to use subqueries, so that UNION and ORDER BY clauses are not on the same level.
Query:
-- Select the 5 students with the highest mark in the `PCM` subject.
(
SELECT *
FROM student
WHERE subject = 'PCM'
ORDER BY studentMarks DESC
LIMIT 5
)
UNION
(
SELECT *
FROM student
WHERE subject = 'PCB'
ORDER BY studentMarks DESC
LIMIT 5
)
UNION
(
SELECT *
FROM student
WHERE subject = 'ART'
ORDER BY studentMarks DESC
LIMIT 5
);
Check out this SQLFiddle to evaluate the result yourself.
This update aims to allow getting more than 5 students in the scenario that many students share the same grade in a particular subject.
Instead of using LIMIT 5 to get the top 5 rows, we use LIMIT 4,1 to get the fifth highest grade and use that to get all students that have a grade more or equal to that in a given subject. Though, if there are < 5 students in a subject LIMIT 4,1 will return NULL. In that case, we want essentially every student, so we use the minimum grade.
To achieve what is described above, you will need to use the following piece of code x times, as many as the subjects you have and join them together using UNION. As can be easily understood, this solution can be used for a small handful of different subjects or the query's extent will become unmaintainable.
Code:
-- Select the students with the top 5 highest marks in the `x` subject.
SELECT *
FROM student
WHERE studentMarks >= (
-- If there are less than 5 students in the subject return them all.
IFNULL (
(
-- Get the fifth highest grade.
SELECT studentMarks
FROM student
WHERE subject = 'x'
ORDER BY studentMarks DESC
LIMIT 4,1
), (
-- Get the lowest grade.
SELECT MIN(studentMarks)
FROM student
WHERE subject = 'x'
)
)
) AND subject = 'x';
Check out this SQLFiddle to evaluate the result yourself.
Alternative:
After some research I found an alternative, simpler query that will yield the same result as the one presented above based on the data you have provided without the need of "hardcoding" every subject in its own query.
In the following solution, we define a couple of variables that help us control the data:
Query:
-- Select the students having the top 5 marks in each subject.
SELECT studentID, studentName, studentMarks, subject FROM
(
-- Use an incremented value to differentiate rows with the same subject.
SELECT *, (@n := if(@s = subject, @n +1, 1)) as n, @s:= subject
FROM student
CROSS JOIN (SELECT @n := 0, @s:= NULL) AS b
) AS a
WHERE n <= 5
ORDER BY subject, studentMarks DESC;
Check out this SQLFiddle to evaluate the result yourself.
Ideas were taken by the following threads:
Below query produces almost what I desired, may this query helps others in future.
SELECT a.studentId, a.studentName, a.StudentMarks,a.subject FROM testquery AS a WHERE
(SELECT COUNT(*) FROM testquery AS b
WHERE b.subject = a.subject AND b.StudentMarks >= a.StudentMarks) <= 2
ORDER BY a.subject ASC, a.StudentMarks DESC