4

Introducing the GREATEST and LEAST T-SQL functions in Azure Synapse Analytics

 3 years ago
source link: https://techcommunity.microsoft.com/t5/azure-synapse-analytics/introducing-the-greatest-and-least-t-sql-functions-in-azure/ba-p/2281979?WT_mc_id=DOP-MVP-4025064
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Introducing the GREATEST and LEAST T-SQL functions in Azure Synapse Analytics

Introducing the GREATEST and LEAST T-SQL functions in Azure Synapse Analytics

Published 04-22-2021 03:07 PM 640 Views

We are excited to announce that the GREATEST and LEAST T-SQL functions are now generally available in Azure Synapse Analytics (serverless SQL pools only).

This post describes the functionality and common use cases of GREATEST and LEAST in Azure Synapse Analytics, as well as how they provide a more concise and efficient solution for developers compared to existing T-SQL alternatives.



Functionality



GREATEST and LEAST are scalar-valued functions and return the maximum and minimum value, respectively, of a list of one or more expressions.



The syntax is as follows:

GREATEST ( expression1 [ ,...expressionN ] )
LEAST ( expression1 [ ,...expressionN ] )


As an example, let’s say we have a table CustomerAccounts and wish to return the maximum account balance for each customer:



CustomerID Checking Savings Brokerage 1001 $ 4,294.10 $ 14,109.84 $ 3,000.01 1002 $ 51,495.00 $ 97,103.43 $ 0.02 1003 $ 10,619.73 $ 33,194.01 $ 5,005.74 1004 $ 24,924.33 $ 203,100.52 $ 10,866.87

Prior to GREATEST and LEAST, we could achieve this through a searched CASE expression:

SELECT CustomerID, GreatestBalance =
    CASE
        WHEN Checking >= Savings and Checking >= Brokerage THEN Checking
        WHEN Savings > Checking and Savings > Brokerage THEN Savings
        WHEN Brokerage > Checking and Brokerage  > Savings THEN Brokerage
    END
FROM CustomerAccounts;


We could alternatively use CROSS APPLY: 

SELECT ca.CustomerID, MAX(T.Balance) as GreatestBalance
FROM CustomerAccounts as ca
CROSS APPLY (VALUES (ca.Checking),(ca.Savings),(ca.Brokerage)) AS T(Balance)
GROUP BY ca.CustomerID;


Other valid approaches include user-defined functions (UDFs) and subqueries with aggregates.

However, as the number of columns or expressions increases, so does the tedium of constructing these queries and the lack of readability and maintainability.



With GREATEST, we can return the same results as the queries above with the following syntax:

SELECT CustomerID, GREATEST(Checking, Savings, Brokerage) AS GreatestBalance 
FROM CustomerAccounts;


Here is the result set:

CustomerID  GreatestBalance
----------- ---------------------
1001            14109.84
1002            97103.43
1003            33194.01
1004           203100.52

(4 rows affected)


Similarly, if you previously wished to return a value that’s capped by a certain amount, you would need to write a statement such as:

DECLARE @Val INT = 75;
DECLARE @Cap INT = 50;
SELECT CASE WHEN @Val > @Cap THEN @Cap ELSE @Val END as CappedAmt;


With LEAST, you can achieve the same result with:

DECLARE @Val INT = 75;
DECLARE @Cap INT = 50;
SELECT LEAST(@Val, @Cap) as CappedAmt;


The syntax for an increasing number of expressions is vastly simpler and more concise with GREATEST and LEAST than with the manual alternatives mentioned above

As such, these functions allow developers to be more productive by avoiding the need to construct lengthy statements to simply find the maximum or minimum value in an expression list.

Common use cases



Constant arguments

One of the simpler use cases for GREATEST and LEAST is determining the maximum or minimum value from a list of constants:

SELECT LEAST ( '6.62', 33.1415, N'7' ) AS LeastVal;


Here is the result set. Note that the return type scale is determined by the scale of the highest precedence argument, in this case float.

LeastVal
--------
6.6200

(1 rows affected)

Local variables



Perhaps we wish to compare column values in a WHERE clause predicate against the maximum value of two local variables:

CREATE TABLE dbo.studies (
    VarX varchar(10) NOT NULL,
    Correlation decimal(4, 3) NULL
);

INSERT INTO dbo.studies VALUES ('Var1', 0.2), ('Var2', 0.825), ('Var3', 0.61);
GO

DECLARE @PredictionA DECIMAL(2,1) = 0.7;
DECLARE @PredictionB DECIMAL(3,2) = 0.65;

SELECT VarX, Correlation
FROM dbo.studies
WHERE Correlation > GREATEST(@PredictionA, @PredictionB);
GO


Here is the result set: 

VarX       Correlation
---------- -----------
Var2              .825

(1 rows affected)

Columns, constants and variables



At times we may want to compare columns, constants and variables together. Here is one such example using LEAST:

CREATE TABLE dbo.products (
    prod_id INT IDENTITY(1,1),
    listprice smallmoney NULL
);

INSERT INTO dbo.products VALUES (14.99), (49.99), (24.99);
GO

DECLARE @PriceX smallmoney = 19.99;

SELECT LEAST(listprice, 40, @PriceX) as LeastPrice
FROM dbo.products;
GO


And the result set:

LeastPrice
------------
     14.99
     19.99
     19.99

Summary



GREATEST and LEAST provide a concise way to determine the maximum and minimum value, respectively, of a list of expressions.

For full documentation of the functions, see GREATEST (Transact-SQL) - SQL Server | Microsoft Docs and LEAST (Transact-SQL) - SQL Server | Microsoft Docs.

These new T-SQL functions will increase your productivity and enhance your experience with Azure Synapse Analytics.



Providing the GREATEST developer experience in Azure is the LEAST we can do.



John Steen, Software EngineerAustin SQL Team

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

%3CLINGO-SUB%20id%3D%22lingo-sub-2281979%22%20slang%3D%22en-US%22%3EIntroducing%20the%20GREATEST%20and%20LEAST%20T-SQL%20functions%20in%20Azure%20Synapse%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281979%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CP%20class%3D%22MsoNormal%22%3EWe%20are%20excited%20to%20announce%20that%20the%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20and%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20T-SQL%20functions%20are%20now%20generally%20available%20in%20Azure%20Synapse%20Analytics%20(serverless%20SQL%20pools%20only).%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThis%20post%20describes%20the%20functionality%20and%20common%20use%20cases%20of%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2011.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20%3C%2FFONT%3Eand%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2011.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%3C%2FFONT%3E%20in%20Azure%20Synapse%20Analytics%2C%20as%20well%20as%20how%20they%20provide%20a%20more%20concise%20and%20efficient%20solution%20for%20developers%20compared%20to%20existing%20T-SQL%20alternatives.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--492906718%22%20id%3D%22toc-hId--492906718%22%3EFunctionality%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20%3C%2FFONT%3Eand%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20%3C%2FFONT%3Eare%20scalar-valued%20functions%20and%20return%20the%20maximum%20and%20minimum%20value%2C%20respectively%2C%20of%20a%20list%20of%20one%20or%20more%20expressions.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThe%20syntax%20is%20as%20follows%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EGREATEST%20(%20expression1%20%5B%20%2C...expressionN%20%5D%20)%0ALEAST%20(%20expression1%20%5B%20%2C...expressionN%20%5D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EAs%20an%20example%2C%20let%E2%80%99s%20say%20we%20have%20a%20table%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20class%3D%22SpellE%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ECustomerAccounts%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FFONT%3Eand%20wish%20to%20return%20the%20maximum%20account%20balance%20for%20each%20customer%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3ECustomerID%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3EChecking%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3ESavings%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3EBrokerage%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E1001%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%204%2C294.10%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2014%2C109.84%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%203%2C000.01%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E1002%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2051%2C495.00%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2097%2C103.43%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%200.02%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E1003%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2010%2C619.73%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2033%2C194.01%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%205%2C005.74%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E1004%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2024%2C924.33%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%20203%2C100.52%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%20class%3D%22lia-align-center%22%3E%24%2010%2C866.87%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EPrior%20to%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20and%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%2C%20we%20could%20achieve%20this%20through%20a%20searched%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ECASE%3C%2FSPAN%3E%20%3C%2FFONT%3Eexpression%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ESELECT%20CustomerID%2C%20GreatestBalance%26nbsp%3B%3D%0A%20%20%20%20CASE%0A%20%20%20%20%20%20%20%20WHEN%20Checking%20%26gt%3B%3D%20Savings%20and%20Checking%20%26gt%3B%3D%20Brokerage%20THEN%20Checking%0A%20%20%20%20%20%20%20%20WHEN%20Savings%20%26gt%3B%20Checking%20and%20Savings%20%26gt%3B%20Brokerage%20THEN%20Savings%0A%20%20%20%20%20%20%20%20WHEN%20Brokerage%20%26gt%3B%20Checking%20and%20Brokerage%20%26nbsp%3B%26gt%3B%20Savings%20THEN%20Brokerage%0A%20%20%20%20END%0AFROM%20CustomerAccounts%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EWe%20could%20alternatively%20use%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ECROSS%20APPLY%3C%2FSPAN%3E%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ESELECT%20ca.CustomerID%2C%20MAX(T.Balance)%20as%20GreatestBalance%0AFROM%20CustomerAccounts%20as%20ca%0ACROSS%20APPLY%20(VALUES%20(ca.Checking)%2C(ca.Savings)%2C(ca.Brokerage))%20AS%20T(Balance)%0AGROUP%20BY%20ca.CustomerID%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EOther%20valid%20approaches%20include%20user-defined%20functions%20(UDFs)%20and%20subqueries%20with%20aggregates.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EHowever%2C%20as%20the%20number%20of%20columns%20or%20expressions%20increases%2C%20so%20does%20the%20tedium%20of%20constructing%20these%20queries%20and%20the%20lack%20of%20readability%20and%20maintainability.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EWith%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%2C%20we%20can%20return%20the%20same%20results%20as%20the%20queries%20above%20with%20the%20following%20syntax%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ESELECT%20CustomerID%2C%20GREATEST(Checking%2C%20Savings%2C%20Brokerage)%20AS%20GreatestBalance%26nbsp%3B%0AFROM%20CustomerAccounts%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22mso-bidi-font-family%3A%20Calibri%3B%20mso-bidi-theme-font%3A%20minor-latin%3B%20color%3A%20black%3B%22%3EHere%20is%20the%20result%20set%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ECustomerID%20%20GreatestBalance%0A-----------%20---------------------%0A1001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2014109.84%0A1002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2097103.43%0A1003%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2033194.01%0A1004%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B203100.52%0A%0A(4%20rows%20affected)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22mso-fareast-font-family%3A%20'Yu%20Mincho'%3B%20mso-fareast-theme-font%3A%20minor-fareast%3B%22%3ESimilarly%2C%20if%20you%20previously%20wished%20to%20return%20a%20value%20that%E2%80%99s%20capped%20by%20a%20certain%20amount%2C%20you%20would%20need%20to%20write%20a%20statement%20such%20as%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EDECLARE%20%40Val%20INT%20%3D%2075%3B%0ADECLARE%20%40Cap%20INT%20%3D%2050%3B%0ASELECT%20CASE%20WHEN%20%40Val%20%26gt%3B%20%40Cap%20THEN%20%40Cap%20ELSE%20%40Val%20END%20as%20CappedAmt%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22mso-fareast-font-family%3A%20'Yu%20Mincho'%3B%20mso-fareast-theme-font%3A%20minor-fareast%3B%22%3EWith%20LEAST%2C%20you%20can%20achieve%20the%20same%20result%20with%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EDECLARE%20%40Val%20INT%20%3D%2075%3B%0ADECLARE%20%40Cap%20INT%20%3D%2050%3B%0ASELECT%20LEAST(%40Val%2C%20%40Cap)%20as%20CappedAmt%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThe%20syntax%20for%20an%20increasing%20number%20of%20expressions%20is%20vastly%20simpler%20and%20more%20concise%20with%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20and%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20than%20with%20the%20manual%20alternatives%20mentioned%20above%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22color%3A%20black%3B%20mso-themecolor%3A%20text1%3B%22%3EAs%20such%2C%20these%20functions%20allow%20developers%20to%20be%20more%20productive%20by%20avoiding%20the%20need%20to%20construct%20lengthy%20statements%20to%20simply%20find%20the%20maximum%20or%20minimum%20value%20in%20an%20expression%20list.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1994606115%22%20id%3D%22toc-hId-1994606115%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-187151652%22%20id%3D%22toc-hId-187151652%22%3ECommon%20use%20cases%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-877713126%22%20id%3D%22toc-hId-877713126%22%3EConstant%20arguments%3C%2FH3%3E%0A%3CP%20class%3D%22MsoNormal%22%3EOne%20of%20the%20simpler%20use%20cases%20for%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%3CSPAN%20style%3D%22mso-bidi-font-family%3A%20Calibri%3B%20mso-bidi-theme-font%3A%20minor-latin%3B%20color%3A%20black%3B%22%3E%20and%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20is%20determining%20the%20maximum%20or%20minimum%20value%20from%20a%20list%20of%20constants%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ESELECT%20LEAST%20(%26nbsp%3B'6.62'%2C%2033.1415%2C%20N'7'%26nbsp%3B)%26nbsp%3BAS%26nbsp%3BLeastVal%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EHere%20is%20the%20result%20set.%20Note%20that%20the%20return%20type%20scale%20is%20determined%20by%20the%20scale%20of%20the%20highest%20precedence%20argument%2C%20in%20this%20case%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3Efloat.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ELeastVal%0A--------%0A6.6200%0A%0A(1%20rows%20affected)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId-867210022%22%20id%3D%22toc-hId-867210022%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH3%20id%3D%22toc-hId-1557771496%22%20id%3D%22toc-hId-1557771496%22%3ELocal%20variables%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EPerhaps%20we%20wish%20to%20compare%20column%20values%20in%20a%20WHERE%20clause%20predicate%20against%20the%20maximum%20value%20of%20two%20local%20variables%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ECREATE%20TABLE%20dbo.studies%20(%0A%20%20%20%20VarX%20varchar(10)%20NOT%20NULL%2C%0A%20%20%20%20Correlation%20decimal(4%2C%203)%20NULL%0A)%3B%0A%0AINSERT%20INTO%20dbo.studies%20VALUES%20('Var1'%2C%200.2)%2C%20('Var2'%2C%200.825)%2C%20('Var3'%2C%200.61)%3B%0AGO%0A%0ADECLARE%20%40PredictionA%20DECIMAL(2%2C1)%20%3D%200.7%3B%0ADECLARE%20%40PredictionB%20DECIMAL(3%2C2)%20%3D%200.65%3B%0A%0ASELECT%20VarX%2C%20Correlation%0AFROM%20dbo.studies%0AWHERE%20Correlation%20%26gt%3B%20GREATEST(%40PredictionA%2C%20%40PredictionB)%3B%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EHere%20is%20the%20result%20set%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EVarX%20%20%20%20%20%20%20Correlation%0A----------%20-----------%0AVar2%20%20%20%20%20%20%20%20%20%20%20%20%20%20.825%0A%0A(1%20rows%20affected)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId-1547268392%22%20id%3D%22toc-hId-1547268392%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH3%20id%3D%22toc-hId--2057137430%22%20id%3D%22toc-hId--2057137430%22%3EColumns%2C%20%3CSPAN%20class%3D%22GramE%22%3Econstants%3C%2FSPAN%3E%20and%20variables%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EAt%20times%20we%20may%20want%20to%20compare%20columns%2C%20%3CSPAN%20class%3D%22GramE%22%3Econstants%3C%2FSPAN%3E%20and%20variables%20together.%20Here%20is%20one%20such%20example%20using%20%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ECREATE%20TABLE%20dbo.products%20(%0A%20%20%20%20prod_id%20INT%20IDENTITY(1%2C1)%2C%0A%20%20%20%20listprice%20smallmoney%20NULL%0A)%3B%0A%0AINSERT%20INTO%20dbo.products%20VALUES%20(14.99)%2C%20(49.99)%2C%20(24.99)%3B%0AGO%0A%0ADECLARE%20%40PriceX%20smallmoney%20%3D%2019.99%3B%0A%0ASELECT%20LEAST(listprice%2C%2040%2C%20%40PriceX)%20as%20LeastPrice%0AFROM%20dbo.products%3B%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EAnd%20the%20result%20set%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3ELeastPrice%0A------------%0A%20%20%20%20%2014.99%0A%20%20%20%20%2019.99%0A%20%20%20%20%2019.99%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId--2067640534%22%20id%3D%22toc-hId--2067640534%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-419872299%22%20id%3D%22toc-hId-419872299%22%3ESummary%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20%3C%2FFONT%3Eand%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20%3C%2FFONT%3Eprovide%20a%20concise%20way%20to%20determine%20the%20maximum%20and%20minimum%20value%2C%20respectively%2C%20of%20a%20list%20of%20expressions.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EFor%20full%20documentation%20of%20the%20functions%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Flogical-functions-greatest-transact-sql%3Fview%3Dazuresqldb-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20style%3D%22mso-comment-continuation%3A%2026%3B%22%3EGREATEST%20(Transact-SQL)%20-%20SQL%20Server%20%7C%20Microsoft%20Docs%3C%2FSPAN%3E%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Flogical-functions-least-transact-sql%3Fview%3Dazuresqldb-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20style%3D%22mso-comment-continuation%3A%2026%3B%22%3ELEAST%20(Transact-SQL)%20-%20SQL%20Server%20%7C%20Microsoft%20Docs%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20style%3D%22mso-comment-continuation%3A%2026%3B%22%3E.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThese%20new%20T-SQL%20functions%20will%20increase%20your%20productivity%20and%20enhance%20your%20experience%20with%20Azure%20Synapse%20Analytics.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EProviding%20the%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20%3C%2FFONT%3Edeveloper%20experience%20in%20Azure%20is%20the%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20%3C%2FFONT%3Ewe%20can%20do.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EJohn%20Steen%2C%20Software%20Engineer%3CBR%20%2F%3EAustin%20SQL%20Team%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2281979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22GreatestLeastSSMS.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274822iB33A6A2D07F951D5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22GreatestLeastSSMS.PNG%22%20alt%3D%22GreatestLeastSSMS.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20are%20excited%20to%20announce%20that%20the%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3EGREATEST%3C%2FSPAN%3E%20%3C%2FFONT%3Eand%20%3CFONT%20size%3D%224%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20line-height%3A%20107%25%3B%20font-family%3A%20Consolas%3B%22%3ELEAST%3C%2FSPAN%3E%20%3C%2FFONT%3ET-SQL%20functions%20are%20now%20generally%20available%20in%20Azure%20Synapse%20Analytics%20(serverless%20SQL%20pools%20only.)%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E

Co-Authors
Version history
Last update:

‎Apr 22 2021 03:06 PM

Updated by:
Labels

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK