Ask the Expert

How to select the highest grade from list of names and ID's

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?


    Requires Free Membership to View

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


This was first published in March 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: