Question: Search for The highest score

Description: There are 7 students in the class. Each of them have their own exam score. You are required to search for the name and score of whoever scores the highest.

Requirement: Provide a SQL statement to get the stated result.

Table Name: MyClass

Name Score
Tan Ti Hun 58
Lim Keng Hong 39
Chee Ming Tek 89
Loh Ai Lian 64
Qi Wei Kee 99
Lawrence Ooi 40
Andrew Png 99



Result:

Name Score
Qi Wei Kee 99
Andrew Png 99

This is my answer:

1. SELECT Name, MAX(Score) FROM MyClass
or
2. SELECT Name, MAX(Score) AS Score FROM MyClass
or
3. SELECT Name, Score FROM MyClass WHERE Score = 99

Does anyone have any idea or other answers for this question?

Come now! Is this your school work? If so, I'm afraid that I am not required to do anything about it ;).

No. I just want more idea. Anyway thank you.

SELECT Name, Score FROM MyClass where Score = (Select Max(Score) from MyClass)

Does it look so simple now?

You got another way too.

SELECT Name, Score FROM MyClass where Score = (Select top 1 Score from MyClass order by Score desc)

Is this what you are looking for?

Cheers!

_________________________
-Vijay G
Strive for Perfection

Vijay,

I don't believe TOP works in MySQL. I believe it is LIMIT.

HTH,

Snib

<><

Sorry about that, That was my mistake. Something went in my ind that this was a MSSQL post. I didnot notice that.

That is why I should "STRIVE FOR PERFECTION" always.

Please ignore about the TOP query there. But you can use that with LIMIT in mysql. Please check Mysql documentation on that.

Cheers!

_________________________
-Vijay G
Strive for Perfection

thanks eveyone! :)

only show the name and the highest score. thank you.