--- title: The GREATEST and LEAST functions arrive in Azure SQL Database description: Microsoft has introduced two brand new TSQL functions to Azure SQL Databases; GREATEST and LEAST, which joined the documented list of TSQL functions online this month. image: https://blog.coeo.com/hubfs/daniele-levis-pelusi-4mpsEm3EGak-unsplash.png --- [![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com) # The GREATEST and LEAST functions arrive in Azure SQL Database # The Coeo Blog ![Michael Kayley](https://blog.coeo.com/hubfs/MichaelCircleLow.jpg) Towards the end of 2020, Microsoft introduced two brand new TSQL functions to Azure SQL Databases; GREATEST and LEAST which joined Microsoft’s documented list of TSQL functions online this month ([GREATEST](https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15)and [LEAST](https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-least-transact-sql?view=sql-server-ver15)). Appropriately named, the GREATEST function takes a list of values and returns the largest of these whilst the LEAST takes a list of values and returns the smallest. They really do work as one would expect. Gone are the days of needing to use workaround alternatives such as CROSS APPLYs or UNION ALL to formulate your queries. It’s worth noting that NULL values are ignored when used with other values and both functions work with various date types including numbers, dates and strings. Let’s walk through some simple examples of these functions in action with different data types **Data Type: INT** SELECT GREATEST(null,1,2,3,4,5) 5 SELECT LEAST(null,1,2,3,4,5) 1 **Data Type: Date** SELECT GREATEST('01/01/1900','01/01/2021') 01/01/2021 SELECT LEAST('01/01/1900','01/01/2021') 01/01/1990 **Data Type: String** SELECT GREATEST('a','b','c') c SELECT LEAST('a','b','c') a ## How does this differ from the existing MAX and MIN functions? MAX and MIN are aggregate functions that provide you with the greatest/least value in a column. They also require grouping or partitions to run. On the other hand, GREATEST and LEAST are single row expressions for comparisons between several columns or expressions. You can use MAX and MIN to mimic the functionality that the GREATEST and LEAST functions offer; but it usually involves cross applying any columns to create an aliased single column which adds unnecessary headaches to your code. ## **Using a mixture of data types** If either function is used when the data type of the values ​​or columns are different; all values will be converted to the data type of highest precedence before the comparison takes place. For example, in the query below, the SQL engine will attempt to convert each value into an int as this is the data type with the highest precedence: SELECT GREATEST(1,'A',7) However, this will fail due to a conversion failure, due to implicate conversion between integers and varchars being unsupported: Conversion failed when converting the varchar value 'A' to data type int.   Will you be using either of these functions in your coding going forward? Drop a message in the comments below.   [![Enjoyed this blog? Sign up for monthly updates](https://no-cache.hubspot.com/cta/default/3356718/c92a8cd5-326e-4f79-a7b8-5a6ab62c29dc.png)](https://cta-redirect.hubspot.com/cta/redirect/3356718/c92a8cd5-326e-4f79-a7b8-5a6ab62c29dc) ### Subscribe to Email Updates ## Related posts --- ### [Secure Your Azure SQL Estate with Azure AD-only authentication](https://blog.coeo.com/secure-your-azure-sql-estate-with-azure-ad-only-authentication) ### [Does SQL Server Get An Extra Hour In Bed?](https://blog.coeo.com/does-sql-server-get-an-extra-hour-in-bed) ### [Database in restoring state and single user mode? See why and how to resolve](https://blog.coeo.com/database-in-restoring-state-and-single-user-mode) ### [Introducing Private Endpoints for Azure SQL Database](https://blog.coeo.com/introducing-private-endpoints-for-azure-sql-database) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | info@coeo.com ## Contact Us By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested. You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**. ## Upcoming Events [See all events](https://www.coeo.com/events/) #### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB [![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) - [Solutions](https://www.coeo.com/solutions/) - [Next Steps](https://www.coeo.com/next-steps/) - [Dedicated Support](https://www.coeo.com/dedicated-support/) - [Case studies](https://www.coeo.com/case-studies/) - [Technologies](https://www.coeo.com/solutions/technologies/) - [Industries](https://www.coeo.com/industries/) - [Finance](https://www.coeo.com/industries/finance/) - [Retail](https://www.coeo.com/industries/retail/) - [Technology](https://www.coeo.com/industries/technology/) - [The Team](https://www.coeo.com/people/) - [Join Us](https://www.coeo.com/careers/) - [Graduate Programme](https://www.coeo.com/graduate-programme/) - [About Coeo](https://www.coeo.com/about-coeo/) - [The Coeo Blog](https://www.coeo.com/blog/) - [Contact us](https://www.coeo.com/contact-us/) - [Privacy Notice](https://www.coeo.com/privacy/) - [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy) - [Events](https://www.coeo.com/events) Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png) Back to top