CODEWITHSUNDEEP
mysql
Saifullah Alam
Saifullah Alam

Reputation: 301

Get 3 highest score in mysql

I have the following table structure:

user_id  english   physics    geography   chemistry

  1        75        60          85          79

I want to show the users top 3 subject they got highest. So here we can see user1 got highest in geography: 85 then in chemistry : 79 and then in english : 75

So I will show later to the user that their top 3 scored subject are Geography, Chemistry and English and also show them the scores.

Please have a look at the table on fiddle: table link

Upvotes: 2

Views: 909

Answers (1)

Darrell
Darrell

Reputation: 648

Do you NEED to use this table structure? This would be MUCH easier if you had 2 tables.

Users(user_id, name) and Grades(subject_name, score, user_id)

You could then query:

SELECT Users.name, subject_name, score FROM Grades 
JOIN Users on Grades.user_id = Users.user_id
WHERE Users.user_id = ?
ORDER BY score DESC LIMIT 3

OR:

SELECT TOP 3 Users.name, subject_name, score FROM Grades 
JOIN Users on Grades.user_id = Users.user_id
WHERE Users.user_id = ?
ORDER BY score DESC

Upvotes: 2

Related Questions