8

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
NullUserException
  • 83,810
  • 28
  • 209
  • 234
WalterJ89
  • 1,025
  • 2
  • 7
  • 24
  • 3
    For what database? GREATEST & LEAST are supported by [PostgreSQL](http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html), [MySQL](http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#function_greatest), [Oracle](http://techonthenet.com/oracle/functions/greatest.php). SQL Server is the only one of the majors that doesn't support GREATEST/LEAST. – OMG Ponies Sep 25 '10 at 17:58
  • 1
    I know that PostgreSQL and MySQL support GREATEST/LEAST. my question involves the actual SQL standard http://en.wikipedia.org/wiki/SQL#Standardization – WalterJ89 Sep 25 '10 at 22:35
  • See also: http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns – Mark Byers May 23 '12 at 12:07
  • UPDATE: Greatest and least where added to [SQL:2023 as optional feature T054](https://modern-sql.com/caniuse/T054). Existing implementation do not always confirm the the new standard in respect to [how NULL values are handled](https://modern-sql.com/caniuse/greatest-least#null). – Markus Winand Jun 30 '23 at 12:59
  • Too bad Sqlite doesn't support this though you can use max(a,b) and min(a,b) but it's not the same. – dataman Jul 31 '23 at 22:30

3 Answers3

5
GREATEST(1,2,3,4,5,6,7) AS number

can become

(select max(tmp) from (
        select 1 tmp from dual
        union all
        select 2 tmp from dual
        union all
        select 3 tmp from dual
        union all
        select 4 tmp from dual
        union all
        select 5 tmp from dual
        union all
        select 6 tmp from dual
        union all
        select 7 tmp from dual
) ) AS number
Never Sleep Again
  • 1,331
  • 1
  • 9
  • 10
4

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
Aillyn
  • 23,354
  • 24
  • 59
  • 84
  • 4
    This won't scale very well to much more than two values. It's fine for the OPs specific example, but doesn't answer the more general question. – Mark Byers Apr 24 '12 at 08:49
3

As of now, GREATEST() and LEAST() are supported in Azure SQL. SQL Server 2022 will have it for on-premises installations.

GREATEST documentation:

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15

Dave Markle
  • 95,573
  • 20
  • 147
  • 170