Search code examples
sqlstandards

GREATEST and LEAST in SQL standard

My understanding is that GREATEST() and LEAST() are not part of the SQL standard, but are very common.

I'm wondering, is there a way to clone the functionality of GREATEST keeping within the SQL standard?

SELECT id, GREATEST(1,2,3,4,5,6,7) AS number FROM table

The fully query:

  SELECT SUBSTR(section,1,2) AS campus, 
           AVG(GREATEST(maximum - enrolled, 0)) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus
Solution

  • You can use the CASE expression:

      SELECT SUBSTR(section,1,2) AS campus, 
           AVG(CASE WHEN maximum - enrolled > 0 
                    THEN maximum - enrolled
                    ELSE 0
               END) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus