Let’s say that we have a table of grades that students received during a school year. The columns are as follows:

  • student_id
  • quarter1
  • quarter2
  • quarter3
  • quarter4

If you had to display the highest grade that a student received for a quarter, how would you go about selecting the data? Your first thought may be to use a CASE statement. As you may know from experience, using this means that you have to write a longer query. Alternatively, as Thom Brown brought to my attention, you can use PostgreSQL’s GREATEST function. Assuming that we have a student table with the specified fields, we could use the following to retrieve the name of the student and that student’s best quarter grade:

SELECT students.first_name,
  students.last_name,
  GREATEST(
    grades.quarter1,
    grades.quarter2,
    grades.quarter3,
    grades.quarter4
  ) AS best_quarter_grade

As you may have guessed, there is also a LEAST function in PostgreSQL which will give you the ability to get the smallest value in a list of expressions.

Previous Solution

My solution to this prior to knowing about the GREATEST and LEAST functions dealt with the MAX, and UNNEST functions. The equivalent query for grabbing the best quarter grade is as follows:

SELECT students.first_name,
  students.last_name,
  (
    SELECT MAX(quarters)
    FROM unnest(ARRAY[
        grades.quarter1,
        grades.quarter2,
        grades.quarter3,
        grades.quarter4
      ]) quarters
  ) AS best_quarter_grade
FROM students
JOIN grades
ON students.id = grades.student_id

This works by converting the array of the four quarter grades into a set of rows. Next I select the maximum of those quarter grades and alias it as best_quarter_grade. Even though this approach works, I recommend using the GREATEST function because it requires less code and most-likely works more efficiently. 8)

Categories: BlogSQL

3 Comments

Thom Brown · July 27, 2011 at 9:43 AM

Actually my first thought was to use greatest() which can be very handy for such situations but is often overlooked:

SELECT first_name, last_name, greatest(quarter1, quarter2, quarter3, quarter4) best_quarter_grade
FROM students
JOIN grades
ON students.id = grades.student_id;

Plus I think there’s a typo in the unnest in your query where there are commas after ‘grades’ instead of full-stop separator.

cwest · July 27, 2011 at 10:09 AM

Thanks @Thom Brown for the great find and the typo hint. I will definitely remember to use this function!

Sulaiman · January 8, 2015 at 4:35 AM

Thanks for the tip. Is there a way to returning which quarter the best grade came from (that is which field was carrying the greatest value)?

Leave a Reply

Your email address will not be published. Required fields are marked *