I want to select the student who has the highest (letter) grade in a list of grades--showing their name and ID. This is a nested query, I assume. How do I do this?
Yes, it's a nested query, also called a subquery or subselect.
Let's say that your list of grades is in a table containing student id, student name, and grade --
001 Smith, Art B 002 Jones, Bill C 003 Baker, Tom A 004 White, Molly A 005 Mason, Mary C etc. 198 Grant, Zack B
As you can see, using a letter grade means there will probably be more than one student with the highest grade.
Here's the SQL --
SELECT StudentID, StudentName, Grade FROM ListOfGrades WHERE grade = (SELECT MIN(Grade) FROM ListOfGrades)
Notice the use of MIN(Grade) in the inner or nested query. This is because alphabetically, A is less than B, which is less than C, and so on. The grade letters are ascending, but the "highest" or best grade is actually the lowest alphabetically. If the grade were a number instead of a letter, then we would use MAX(Grade) to get the highest grade number.
The subselect or inner query is not correlated to the outer query, which means that the database can evaluate it once, temporarily save the result, and apply this result in the outer query's WHERE clause to each row that it retrieves from the outer table (which happens to be the same table). Because the database evaluates the uncorrelated subquery only once, the resulting performance of the whole query is very good.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums. Also, give us your feedback about this answer in the "Sound Off" forum.
- Ask the Experts yourself: Our SQL guru is waiting to answer your toughest questions.
This was first published in March 2001