DEV Community

Olesia Dudareva
Olesia Dudareva

Posted on • Edited on

4 1

Don't miss out on the new features in SQL Server 16.x

The latest version of SQL Server has some exciting updates as well as some very useful new features. Today I would like to look at GREATEST and LEAST, two opposite functions for getting the maximum and minimum values in a list.

GREATEST & LEAST

They respectively return the maximum and minimum values from the list of values. There are some restrictions: the list must contain at least one value; the maximum number of values in the list is 254.
Both functions do not support some data types, for example, cursor, geometry, geography, image, table, ntext, text, xml.

Syntax

GREATEST(value1, value2,)
Enter fullscreen mode Exit fullscreen mode
LEAST(value1, value2,)
Enter fullscreen mode Exit fullscreen mode

Input parameters can be, for instance, variables, column names, functions, and any combination of arithmetic, and string operators.

Return data type

The functions return the data type with the highest precedence. If all values in the list are of the same type, it will be returned by the functions. If not, they will try to convert them. As a result, it is important that all values in the list be convertible.

Examples

  • Constant numbers: the scale = 4 because it is determined by the scale of 4.7890 (the highest precedence data type).
SELECT GREATEST(4.7890, '5.1', N'3');

Output:
-------
 5.1000
Enter fullscreen mode Exit fullscreen mode
SELECT LEAST(4.7890, '5.1', N'3');

Output:
-------
 3.0000
Enter fullscreen mode Exit fullscreen mode
  • Strings
SELECT GREATEST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
John Smith
Enter fullscreen mode Exit fullscreen mode
SELECT LEAST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
Anna Black
Enter fullscreen mode Exit fullscreen mode
  • Combination of variables, table column and constant
CREATE TABLE Currency (
    Currency_Name VARCHAR(3),
    Currency_Rate DECIMAL(10,4)
    );

INSERT INTO Currency
VALUES ('USD', 0.82),
    ('USD', 0.8101),
    ('USD', 0.8058);
GO

DECLARE @Default_Rate DECIMAL(10, 4) = 0.8195;

SELECT Currency_Name,
    Currency_Rate,
    GREATEST(Currency_Rate, 0.8000, @Default_Rate) AS GreatestRate,
    LEAST(Currency_Rate, 0.8000, @Default_Rate) AS LeastRate
FROM Currency;
GO

Output:
Currency_Name Currency_Rate GreatestRate LeastRate   
------------- ------------- ------------ ------------
USD                   .8200        .8200        .8000
USD                   .8101        .8195        .8000
USD                   .8058        .8195        .8000
Enter fullscreen mode Exit fullscreen mode

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (2)

Collapse
 
rozhnev profile image
Slava Rozhnev

I posted similar article (dev.to/rozhnev/sql-server-2022-log...)

Collapse
 
notte profile image
Olesia Dudareva

Great! I just added a little more information about these amazing functions. They can definitely be very useful, but as usual, there are some limitations that developers should be aware of.

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay