Manage Learn to apply best practices and optimize your operations.

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?

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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.