Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 24th, 2004, 11:03 PM
Authorized User
 
Join Date: Oct 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Urgent! Please help.

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?

 
Old May 25th, 2004, 06:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote: You are required to search for the name and score of whoever scores the highest.
Come now! Is this your school work? If so, I'm afraid that I am not required to do anything about it ;).
 
Old May 25th, 2004, 07:05 AM
Authorized User
 
Join Date: Oct 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old May 25th, 2004, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 25th, 2004, 04:51 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,285
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Vijay,

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

HTH,

Snib

<><
 
Old May 25th, 2004, 04:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 26th, 2004, 01:19 AM
Authorized User
 
Join Date: Oct 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks eveyone! :)

 
Old May 26th, 2004, 01:23 AM
Authorized User
 
Join Date: Oct 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
urgent deb_kareng ASP.NET 2.0 Professional 1 August 13th, 2007 07:29 AM
it's urgent deb_kareng ASP.NET 2.0 Professional 3 August 7th, 2007 07:40 AM
urgent help yash_coolbuddy_forindia BOOK: Wrox's ASP.NET 2.0 Visual Web Developer 2005 Express Edition Starter ISBN: 978-0-7645-8807-5 1 May 7th, 2007 08:40 AM
urgent???????????? nsr35 Beginning VB 6 1 October 3rd, 2005 10:57 AM
urgent ??????????????? nsr35 Pro VB Databases 0 October 3rd, 2005 04:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.