1

I am having a table student. I need top 3 highest records from each group. The query should work for dynamically added groups. Without setting default value of groups in the query.

student table:

id    name    mark       subject
--------------------------------------
1     kannan     60         French
2     balan      77         French
3     raja       88         French
4     sheik      78         French
5     satheesh   98         French
6     Ravi       90         French
7     Vishnu     90         English
8     siva       100        English
9     suresh     50         English
10    ramesh     59         English
11    ganesh     97         English
12    david      58         English

Expected Result:

name      mark    subject
---------------------------
Ravi      90      French
Satheesh  98      French
raja      88      French
siva      100     English
ganesh    97      English
ramesh    59      English

Query i tried:

SELECT name, mark, subject from (SELECT name, mark, subject order by mark 
desc LIMIT 3) as ss group by subject, mark;

But I am not getting the correct values.

Kannan K
  • 4,411
  • 1
  • 11
  • 25
  • you need to create procedure or function – krishn Patel Jan 11 '18 at 12:45
  • 2
    Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – rlanvin Jan 11 '18 at 13:24

1 Answers1

0
(SELECT NAME ,mark, SUBJECT FROM `student ` WHERE SUBJECT = 'French' ORDER BY  mark DESC LIMIT 3)
UNION 
(SELECT NAME ,mark, SUBJECT FROM `student ` WHERE SUBJECT = 'English' ORDER BY  mark DESC LIMIT 3)
Sameer Rangrez
  • 162
  • 5