Q

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close