SQL Server GREATEST and LEAST Functions for Range of a Data Set

Problem

I need to calculate the range of a data set in SQL Server. The problem is that the different data values are not stored as rows but as columns. Is there an easy way to calculate this using T-SQL?

Solution

You can calculate a couple of statistical values to describe a data set. The mean and the median are well-known averages, but you also have the mode and the range. The range of a data set is the difference between the largest and smallest values in the set.

For example, if we have the following values: {1,2,3,4,5,6,7,8,9,10}, the range is (10-1) = 9.

Calculating the range for values stored as a single column is straightforward using the MIN and MAX functions:

SELECT TheRange = MAX(mycolumn) - MIN(mycolumn)
FROM myTable;

However, what if the values are not stored as rows inside a column but as values in different columns? This would mean you would have the find the highest and lowest values across columns, which is not supported by the MIN and MAX functions. Many database vendors have had the GREATEST and LEAST functions in their version of SQL, but in SQL Server these functions have been absent for a long time. Recently, they have been added to Azure SQL DB and are now available in SQL Server 2022.

At the time of writing, SQL Server 2022 is still in preview (RC0 was used to write this tip). This means functionality or features of SQL Server might change, disappear, or be added in the final release.

Calculating the Range

The use case for the calculation is as follows: a digital meter tracks a household's electricity consumption. Every 15 minutes, the consumed kWh of the past 15 minutes is registered and sent to the database. Since there will always be 96 quarters in a day (24 * 4 = 96), the database table tracking the consumption has 96 columns—one column for each quarter of the day. Whether or not this is a good design decision is out-of-scope for this tip. Let's also assume we're working with UTC dates and no daylight savings time.

Sample Data

The following script creates a table with all the necessary columns:

DROP TABLE IF EXISTS dbo.ElectricityConsumption;
CREATE TABLE dbo.ElectricityConsumption
(ID INT IDENTITY(1,1) NOT NULL
,CustomerCode VARCHAR(20) NOT NULL
,MeasurementDay DATE NOT NULL
,Measurement1   NUMERIC(10,5) NULL,Measurement2   NUMERIC(10,5) NULL,Measurement3   NUMERIC(10,5) NULL,Measurement4   NUMERIC(10,5) NULL,Measurement5   NUMERIC(10,5) NULL
,Measurement6   NUMERIC(10,5) NULL,Measurement7   NUMERIC(10,5) NULL,Measurement8   NUMERIC(10,5) NULL,Measurement9   NUMERIC(10,5) NULL,Measurement10  NUMERIC(10,5) NULL
,Measurement11  NUMERIC(10,5) NULL,Measurement12  NUMERIC(10,5) NULL,Measurement13  NUMERIC(10,5) NULL,Measurement14  NUMERIC(10,5) NULL,Measurement15  NUMERIC(10,5) NULL
,Measurement16  NUMERIC(10,5) NULL,Measurement17  NUMERIC(10,5) NULL,Measurement18  NUMERIC(10,5) NULL,Measurement19  NUMERIC(10,5) NULL,Measurement20  NUMERIC(10,5) NULL
,Measurement21  NUMERIC(10,5) NULL,Measurement22  NUMERIC(10,5) NULL,Measurement23  NUMERIC(10,5) NULL,Measurement24  NUMERIC(10,5) NULL,Measurement25  NUMERIC(10,5) NULL
,Measurement26  NUMERIC(10,5) NULL,Measurement27  NUMERIC(10,5) NULL,Measurement28  NUMERIC(10,5) NULL,Measurement29  NUMERIC(10,5) NULL,Measurement30  NUMERIC(10,5) NULL
,Measurement31  NUMERIC(10,5) NULL,Measurement32  NUMERIC(10,5) NULL,Measurement33  NUMERIC(10,5) NULL,Measurement34  NUMERIC(10,5) NULL,Measurement35  NUMERIC(10,5) NULL
,Measurement36  NUMERIC(10,5) NULL,Measurement37  NUMERIC(10,5) NULL,Measurement38  NUMERIC(10,5) NULL,Measurement39  NUMERIC(10,5) NULL,Measurement40  NUMERIC(10,5) NULL
,Measurement41  NUMERIC(10,5) NULL,Measurement42  NUMERIC(10,5) NULL,Measurement43  NUMERIC(10,5) NULL,Measurement44  NUMERIC(10,5) NULL,Measurement45  NUMERIC(10,5) NULL
,Measurement46  NUMERIC(10,5) NULL,Measurement47  NUMERIC(10,5) NULL,Measurement48  NUMERIC(10,5) NULL,Measurement49  NUMERIC(10,5) NULL,Measurement50  NUMERIC(10,5) NULL
,Measurement51  NUMERIC(10,5) NULL,Measurement52  NUMERIC(10,5) NULL,Measurement53  NUMERIC(10,5) NULL,Measurement54  NUMERIC(10,5) NULL,Measurement55  NUMERIC(10,5) NULL
,Measurement56  NUMERIC(10,5) NULL,Measurement57  NUMERIC(10,5) NULL,Measurement58  NUMERIC(10,5) NULL,Measurement59  NUMERIC(10,5) NULL,Measurement60  NUMERIC(10,5) NULL
,Measurement61  NUMERIC(10,5) NULL,Measurement62  NUMERIC(10,5) NULL,Measurement63  NUMERIC(10,5) NULL,Measurement64  NUMERIC(10,5) NULL,Measurement65  NUMERIC(10,5) NULL
,Measurement66  NUMERIC(10,5) NULL,Measurement67  NUMERIC(10,5) NULL,Measurement68  NUMERIC(10,5) NULL,Measurement69  NUMERIC(10,5) NULL,Measurement70  NUMERIC(10,5) NULL
,Measurement71  NUMERIC(10,5) NULL,Measurement72  NUMERIC(10,5) NULL,Measurement73  NUMERIC(10,5) NULL,Measurement74  NUMERIC(10,5) NULL,Measurement75  NUMERIC(10,5) NULL
,Measurement76  NUMERIC(10,5) NULL,Measurement77  NUMERIC(10,5) NULL,Measurement78  NUMERIC(10,5) NULL,Measurement79  NUMERIC(10,5) NULL,Measurement80  NUMERIC(10,5) NULL
,Measurement81  NUMERIC(10,5) NULL,Measurement82  NUMERIC(10,5) NULL,Measurement83  NUMERIC(10,5) NULL,Measurement84  NUMERIC(10,5) NULL,Measurement85  NUMERIC(10,5) NULL
,Measurement86  NUMERIC(10,5) NULL,Measurement87  NUMERIC(10,5) NULL,Measurement88  NUMERIC(10,5) NULL,Measurement89  NUMERIC(10,5) NULL,Measurement90  NUMERIC(10,5) NULL
,Measurement91  NUMERIC(10,5) NULL,Measurement92  NUMERIC(10,5) NULL,Measurement93  NUMERIC(10,5) NULL,Measurement94  NUMERIC(10,5) NULL,Measurement95  NUMERIC(10,5) NULL
,Measurement96  NUMERIC(10,5) NULL);

With the following script, we generate random sample data for five customers:

WITH cte_customers AS
(
    SELECT CustomerCode = 'A'
    UNION ALL
    SELECT 'B'
    UNION ALL
    SELECT 'C'
    UNION ALL
    SELECT 'D'
    UNION ALL
    SELECT 'E'
)
,   cte_quarters AS
(
    SELECT MeasQuarter = [value]
    FROM GENERATE_SERIES(1,96)
)
,   cte_randomdata AS
(
    SELECT
         CustomerCode
        ,MeasurementDay         = CONVERT(DATE,SYSDATETIME())
        ,MeasQuarter
        ,ElectricityConsumption = RAND(CAST(CAST(NEWID() AS VARBINARY(16)) AS INT))
    FROM cte_customers
    CROSS JOIN cte_quarters
)
INSERT INTO dbo.ElectricityConsumption
(
     CustomerCode
    ,MeasurementDay
    ,Measurement1,Measurement2,Measurement3,Measurement4,Measurement5,Measurement6,Measurement7,Measurement8,Measurement9,Measurement10
    ,Measurement11,Measurement12,Measurement13,Measurement14,Measurement15,Measurement16,Measurement17,Measurement18,Measurement19,Measurement20
    ,Measurement21,Measurement22,Measurement23,Measurement24,Measurement25,Measurement26,Measurement27,Measurement28,Measurement29,Measurement30
    ,Measurement31,Measurement32,Measurement33,Measurement34,Measurement35,Measurement36,Measurement37,Measurement38,Measurement39,Measurement40
    ,Measurement41,Measurement42,Measurement43,Measurement44,Measurement45,Measurement46,Measurement47,Measurement48,Measurement49,Measurement50
    ,Measurement51,Measurement52,Measurement53,Measurement54,Measurement55,Measurement56,Measurement57,Measurement58,Measurement59,Measurement60
    ,Measurement61,Measurement62,Measurement63,Measurement64,Measurement65,Measurement66,Measurement67,Measurement68,Measurement69,Measurement70
    ,Measurement71,Measurement72,Measurement73,Measurement74,Measurement75,Measurement76,Measurement77,Measurement78,Measurement79,Measurement80
    ,Measurement81,Measurement82,Measurement83,Measurement84,Measurement85,Measurement86,Measurement87,Measurement88,Measurement89,Measurement90
    ,Measurement91,Measurement92,Measurement93,Measurement94,Measurement95,Measurement96
)
SELECT *
FROM cte_randomdata
PIVOT
(
    SUM(ElectricityConsumption)
    FOR MeasQuarter IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
                        [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
                        [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
                        [31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
                        [41],[42],[43],[44],[45],[46],[47],[48],[49],[50],
                        [51],[52],[53],[54],[55],[56],[57],[58],[59],[60],
                        [61],[62],[63],[64],[65],[66],[67],[68],[69],[70],
                        [71],[72],[73],[74],[75],[76],[77],[78],[79],[80],
                        [81],[82],[83],[84],[85],[86],[87],[88],[89],[90],
                        [91],[92],[93],[94],[95],[96])
) AS pivottable;

Let's break this script down:

In the common table expression (CTE) cte_customers, we generate five rows. Each row is a separate customer (A to E).
Using the new GENERATE_SERIES function, we generate a list of all numbers between 1 and 96 in the CTE cte_quarters.
In the CTE cte_randomdata, we cross join both generated lists together, which results in 96 * 5 = 480 rows. To generate electricity consumption, we use the RAND() function.
This gives us all the data needed in rows, but we want the energy consumption in 96 columns. This can be done using the PIVOT function.
Finally, the data is inserted into the table.

We have to be careful with the RAND function. If we don't specify a seed value, we get the same random value across all rows:

The NEWID() function generates a new uniqueidentifier for each row. By converting this to an integer and passing it as a seed value to the RAND function, we get a new random value for each row.

The final sample data looks like this:

Using GREATEST and LEAST to calculate the Range

Before SQL Server 2022, we used alternative solutions to find the minimum or maximum across columns. Some of those are explained in the tip Find MAX value from multiple columns in a SQL Server table. If you're using an older version of SQL Server, you need to resort to the alternatives.

By using the new functions, we can use the following T-SQL statement to calculate the range:

SELECT
     CustomerCode
    ,MeasurementDay
    ,MeasurementRange =
        GREATEST(Measurement1,Measurement2,Measurement3,Measurement4,Measurement5,Measurement6,Measurement7,Measurement8,Measurement9,Measurement10
        ,Measurement11,Measurement12,Measurement13,Measurement14,Measurement15,Measurement16,Measurement17,Measurement18,Measurement19,Measurement20
        ,Measurement21,Measurement22,Measurement23,Measurement24,Measurement25,Measurement26,Measurement27,Measurement28,Measurement29,Measurement30
        ,Measurement31,Measurement32,Measurement33,Measurement34,Measurement35,Measurement36,Measurement37,Measurement38,Measurement39,Measurement40
        ,Measurement41,Measurement42,Measurement43,Measurement44,Measurement45,Measurement46,Measurement47,Measurement48,Measurement49,Measurement50
        ,Measurement51,Measurement52,Measurement53,Measurement54,Measurement55,Measurement56,Measurement57,Measurement58,Measurement59,Measurement60
        ,Measurement61,Measurement62,Measurement63,Measurement64,Measurement65,Measurement66,Measurement67,Measurement68,Measurement69,Measurement70
        ,Measurement71,Measurement72,Measurement73,Measurement74,Measurement75,Measurement76,Measurement77,Measurement78,Measurement79,Measurement80
        ,Measurement81,Measurement82,Measurement83,Measurement84,Measurement85,Measurement86,Measurement87,Measurement88,Measurement89,Measurement90
        ,Measurement91,Measurement92,Measurement93,Measurement94,Measurement95,Measurement96)
    -
        LEAST(Measurement1,Measurement2,Measurement3,Measurement4,Measurement5,Measurement6,Measurement7,Measurement8,Measurement9,Measurement10
        ,Measurement11,Measurement12,Measurement13,Measurement14,Measurement15,Measurement16,Measurement17,Measurement18,Measurement19,Measurement20
        ,Measurement21,Measurement22,Measurement23,Measurement24,Measurement25,Measurement26,Measurement27,Measurement28,Measurement29,Measurement30
        ,Measurement31,Measurement32,Measurement33,Measurement34,Measurement35,Measurement36,Measurement37,Measurement38,Measurement39,Measurement40
        ,Measurement41,Measurement42,Measurement43,Measurement44,Measurement45,Measurement46,Measurement47,Measurement48,Measurement49,Measurement50
        ,Measurement51,Measurement52,Measurement53,Measurement54,Measurement55,Measurement56,Measurement57,Measurement58,Measurement59,Measurement60
        ,Measurement61,Measurement62,Measurement63,Measurement64,Measurement65,Measurement66,Measurement67,Measurement68,Measurement69,Measurement70
        ,Measurement71,Measurement72,Measurement73,Measurement74,Measurement75,Measurement76,Measurement77,Measurement78,Measurement79,Measurement80
        ,Measurement81,Measurement82,Measurement83,Measurement84,Measurement85,Measurement86,Measurement87,Measurement88,Measurement89,Measurement90
        ,Measurement91,Measurement92,Measurement93,Measurement94,Measurement95,Measurement96)
FROM dbo.ElectricityConsumption;

Or in pseudocode, for readability:

SELECT
     CustomerCode
    ,MeasurementDay
    ,MeasurementRange = GREATEST(Measurement1,…,Measurement96) - LEAST(Measurement1,… ,Measurement96)
FROM dbo.ElectricityConsumption;

This gives the following result (which will be different on your machine):

Next Steps

Stay tuned for more SQL Server 2022 tips on the MSSQLTips website!
If you want to try it out, you can create a pre-configured virtual machine in Azure. You can start a free trial here. More info about the SQL Server 2022 release can be found here.
You can find more SQL Server 2022 tips in this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-10-03

How to calculate the Range of a Data Set using the GREATEST and LEAST SQL Server 2022 Functions