PostgreSQL – MAX of Two or More Fields

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)