T-SQL: MAX vs. TOP 1 - Which is better??

Community Member Microsoft Employee

Like ( 0 )

Report

By Steve Endow

If you need to get the largest value for a field, should you use MAX in your query? Or should you use TOP 1 with ORDER BY?

Which is better? Which is faster? Is that always true?

Do you think you know the answer?

Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.

The results may surprise you!

Did I miss anything or make any mistakes in my testing? Are there other considerations when choosing between MAX vs. TOP 1?

Here are the queries that I used in my testing. Note that your results will vary depending on how much data you have in your tables and your SQL Server version.

--MAX vs TOP 1 with ORDER BY

SET STATISTICS IO ON

SELECT MAX(DOCDATE) AS DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'

SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC

SET STATISTICS IO OFF

SET STATISTICS IO ON;

WITH cteMaxDate (DOCDATE) AS

(

SELECT MAX(DOCDATE) FROM PM10000 WHERE VENDORID = 'ACETRAVE0001'

UNION

SELECT MAX(DOCDATE) FROM PM20000 WHERE VENDORID = 'ACETRAVE0001'

UNION

SELECT MAX(DOCDATE) FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'

)

SELECT MAX(DOCDATE) AS DOCDATE FROM cteMaxDate;

WITH cteMaxDate2 (DOCDATE) AS

(

SELECT TOP 1 DOCDATE FROM PM10000 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC

UNION

SELECT TOP 1 DOCDATE FROM PM20000 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC

UNION

SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC

)

SELECT MAX(DOCDATE) AS DOCDATE FROM cteMaxDate2;

SET STATISTICS IO OFF;

SELECT COUNT(*) FROM SEE30303 --73,069 records

SELECT TOP 10 * FROM SEE30303

SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE')

SELECT TOP 1 DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE') ORDER BY DATE1 DESC

SET STATISTICS IO OFF;

SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303

SELECT TOP 1 DATE1 FROM SEE30303 ORDER BY DATE1 DESC

SET STATISTICS IO OFF;

SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303 OPTION (MAXDOP 1)

SELECT TOP 1 DATE1 FROM SEE30303 ORDER BY DATE1 DESC OPTION (MAXDOP 1)

SET STATISTICS IO OFF;

SELECT COUNT(*) AS Rows FROM IV30500

SELECT TOP 100 * FROM IV30500

SET STATISTICS IO ON;

SELECT MAX(POSTEDDT) AS POSTEDDT FROM IV30500 --OPTION (MAXDOP 1)

SELECT TOP 1 POSTEDDT FROM IV30500 ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)

SET STATISTICS IO OFF;

SET STATISTICS IO ON;

SELECT MAX(POSTEDDT) AS POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN '2017-01-01' AND '2017-12-31' --OPTION (MAXDOP 1)

SELECT TOP 1 POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN '2017-01-01' AND '2017-12-31' ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)

SET STATISTICS IO OFF;

SET STATISTICS IO ON;

SELECT MAX(TRXSORCE) AS POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG')

SELECT TOP 1 TRXSORCE FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') ORDER BY TRXSORCE DESC

SET STATISTICS IO OFF;

USE [TWO]

CREATE NONCLUSTERED INDEX NCI_IV30500_ITEMNMBR

ON [dbo].[IV30500] ([ITEMNMBR])

INCLUDE ([TRXSORCE])

USE [TWO]

DROP INDEX IV30500.NCI_IV30500_ITEMNMBR

Steve Endow is a Microsoft MVP in Los Angeles. He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.