GREATEST and LEAST in SQL standard

sql, standards

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

Problem

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

Original source

Related problems