How to retrieve the largest value among multiple columns in a SQL Server table

Views: 15.063 views

In this article

Reading Time: 2 minutes

Hello readers,
Good night!

In this post I will briefly demonstrate how to retrieve the largest value between multiple columns in a SQL Server table. I've seen several solutions to this need, but are you using the most performative of them?

Generating a test mass

To make it easier to see the results, let's create some test data:

IF ( OBJECT_ID('tempdb..##Teste') IS NOT NULL ) DROP TABLE ##Teste
CREATE TABLE ##Teste (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    Nome NVARCHAR(40),
    Data1 DATETIME,
    Data2 DATETIME,
    Data3 DATETIME
)

DECLARE 
    @Dt_Inicial DATETIME = '1900-01-01', 
    @Dt_Final DATETIME = '2099-04-02',
    @Contador INT = 1

WHILE(@Dt_Inicial <= @Dt_Final)
BEGIN
    
    INSERT INTO ##Teste ( Nome, Data1, Data2, Data3 )
    SELECT
        'Teste ' + CAST(@Contador AS VARCHAR(20)), 
        @Dt_Inicial, 
        DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE()), 
        DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE())

    SET @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial)
    SET @Contador = @Contador + 1

END

SELECT * FROM ##Teste

IF ( OBJECT_ID('tempdb..##Teste') IS NOT NULL ) DROP TABLE ##Teste

CREATE TABLE ##Teste (

ID INT IDENTITY(1, 1) PRIMARY KEY,

Nome NVARCHAR(40),

Data1 DATETIME,

Data2 DATETIME,

Data3 DATETIME

)

DECLARE

@Dt_Inicial DATETIME = '1900-01-01',

@Dt_Final DATETIME = '2099-04-02',

@Contador INT = 1

WHILE(@Dt_Inicial <= @Dt_Final)

BEGIN

INSERT INTO ##Teste ( Nome, Data1, Data2, Data3 )

SELECT

'Teste ' + CAST(@Contador AS VARCHAR(20)),

@Dt_Inicial,

DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE()),

DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE())

SET @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial)

SET @Contador = @Contador + 1

END

SELECT * FROM ##Teste

Original Data:

Expected outcome:

Solution 1 - Using VALUES

SELECT 
    ID, 
    (
        SELECT MAX(UltimoAcesso)
        FROM (VALUES (Data1),(Data2),(Data3)) AS UltimoAcesso(UltimoAcesso)
    ) AS UltimoAcesso
FROM 
    ##Teste

SELECT

ID,

(

SELECT MAX(UltimoAcesso)

FROM (VALUES (Data1),(Data2),(Data3)) AS UltimoAcesso(UltimoAcesso)

) AS UltimoAcesso

FROM

##Teste

Solution 2 - Using UNPIVOT

SELECT
    ID,
    MAX(UltimoAcesso) AS UltimoAcesso
FROM
    ##Teste 
    UNPIVOT ( UltimoAcesso FOR DateVal IN ( Data1, Data2, Data3 ) ) AS u
GROUP BY
    ID,
    Nome

SELECT

ID,

MAX(UltimoAcesso) AS UltimoAcesso

FROM

##Teste

UNPIVOT ( UltimoAcesso FOR DateVal IN ( Data1, Data2, Data3 ) ) AS u

GROUP BY

ID,

Nome

Solution 3 - Using UNION

SELECT 
    ID, 
    MAX(UltimoAcesso) AS UltimoAcesso
FROM
(
    SELECT ID, Data1 AS UltimoAcesso
    FROM ##Teste
    
    UNION 

    SELECT ID, Data2 AS UltimoAcesso
    FROM ##Teste
    
    UNION 

    SELECT ID, Data3 AS UltimoAcesso
    FROM ##Teste
) ud
GROUP BY 
    ID

SELECT

ID,

MAX(UltimoAcesso) AS UltimoAcesso

FROM

(

SELECT ID, Data1 AS UltimoAcesso

FROM ##Teste

UNION

SELECT ID, Data2 AS UltimoAcesso

FROM ##Teste

UNION

SELECT ID, Data3 AS UltimoAcesso

FROM ##Teste

) ud

GROUP BY

Performance test

Once we understand the 3 solutions proposed above, let's test performance to identify which runs at the shortest possible time and cost. Thus, we can say what should be the best solution adopted for this situation:

1 Solution