EXPERT RESPONSE
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
|
