By Steve Endow
When you need the highest value for a field, should you use MAX in your query? Or is using TOP 1 with ORDER BY the better option?
Which approach is superior? Which one is faster? Is one consistently better than the other?
Think you have the answer?
Before you decide, watch my video below where I compare MAX against TOP 1 on various Dynamics GP tables. The findings might surprise you!
Did my testing overlook anything or contain any errors? When deciding between MAX and TOP 1, are there any other factors to consider?
Below are the queries I used in my tests. Remember that your results may differ depending on the volume of data 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 ‘2024-02-20T10:50:14Z’ AND ‘2024-05-08T00:56:49Z’ –OPTION (MAXDOP 1)
SELECT TOP 1 POSTEDDT FROM IV30500 WHERE ITEMNMBR IN (‘ARM’, ‘FTRUB’, ‘100XLG’) AND POSTEDDT BETWEEN ‘2024-06-05T19:18:53Z’ AND ‘2023-04-25T18:52:47Z’ 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] GO CREATE NONCLUSTERED INDEX NCI_IV30500_ITEMNMBR ON [dbo].[IV30500] ([ITEMNMBR]) INCLUDE ([TRXSORCE]) GO
USE [TWO] GO DROP INDEX IV30500.NCI_IV30500_ITEMNMBR GO
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.
You can also find him on Twitter, YouTube, and Google+
http://www.precipioservices.com
