CODEWITHSUNDEEP
mysql
uneeb meer
uneeb meer

Reputation: 973

mysql query to get highest score

I have a requirement of a query which i need to build it contains two tables and top three max records from the joined columns let me share the requirements first.

return the first,second and third scorer against each category columns to show are

category,student_id,name,college_name and score 
order by category ascending and college_name ascending

schema

create table students(
id INT autoincrement,
name varchar(255),
college_name varchar(255)
)

create table participations(
participant_id INT autoincrement,
student_id INT,
category varchar(255)
score INT
)

this is what i've done so far

select participations.CATEGORY , STUDENTS.id , STUDENTS.name,STUDENTS.college_name
, participations.category ,participations.score
from students
inner join participations on participations.student_id=students.id
order by participations.CATEGORY asc

while this query works i'm not getting the expected output. the issues are listed below

not getting only the first , second and third highest score against college(the above query shows all colleges and score)

sample data! enter image description here

expected output! Expected Input

Can Anyone help me out?

Upvotes: 1

Views: 586

Answers (2)

SandunAmarathunga
SandunAmarathunga

Reputation: 109

Try DESC LIMIT 3 instead of asc.

let me know whether this worked or not.

Upvotes: 0

Rahul Biswas
Rahul Biswas

Reputation: 3467

Use DENSE_RANK() because same marks can get multiple students per category.

-- MySQL (v5.8)
SELECT p.category
     , s.id student_id
     , s.name student_name
     , s.college_name
     , p.score
FROM students s
INNER JOIN (SELECT category
                 , student_id
                 , score
                 , DENSE_RANK() OVER (PARTITION BY category ORDER BY score DESC) row_num
            FROM participations) p
        ON s.id = p.student_id
       AND row_num <= 3
ORDER BY p.category
       , p.row_num;

Please check this url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=34188b3b46b703299a47322f8da6647e

Upvotes: 1

Related Questions