Let’s say that we have a table of grades that students received during a school year. The columns are as follows:
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.
My solution to this prior to knowing about the
LEAST functions dealt with the
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)
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
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)?