TOP vs Max/Min: Is there a difference?

Question

TOP vs Max/Min: Is there a difference?

Mike Byrd

Default port

Points: 1458
More actions
December 16, 2018 at 10:17 pm

#409385

Comments posted to this topic are about the item TOP vs Max/Min: Is there a difference?
Mike Byrd

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I would say that is dependent on what you are attempting to return. If it is the min or max of a specific column, you may be correct.

Mike C SSC-Insane Points: 23224 More actions · Answer 2

A useful test might be to sort by a different column than you're selecting. Sort by date, name, etc., and retrieve a numeric value, for instance.

gotqn SSC Veteran Points: 277 More actions · Answer 3

The queries are not the same. You are sorting by constant and you should order sort by the id in order to get the same results, right?

denisock Newbie Points: 7 More actions · Answer 4

"order by 1" in both top-queries, is it correct!?

David.Poole SSC Guru Points: 75898 More actions · Answer 5

The examples are on a clustered index. Try it on a non-clustered index and also on an unindexed column.

LinkedIn Profile

steve.powell 14027 SSC Veteran Points: 246 More actions · Answer 6

Logically the two operations would be different and give different results. ORDER BY <column> ASC/DESC with TOP (X) may be used to obtain the maximum or minimum value from a result set, but it's doing so at the row level and will include the unique RowID of that row (even if it's hidden) so it can associate the returned value with the rest of the row. It's a redundant step for what you want, but the optimiser does this because it's part of the core process for handling ORDER BY and TOP clauses. MIN/MAX, however, returns a single value from the dataset: It's independent of the remaining values unless you include a GROUP BY clause: Then it knows to associate the MIN/MAX value to the grouped value which will then change the execution plan to reflect this.

steve.powell 14027 SSC Veteran Points: 246 More actions · Answer 7

denisock: Yes, ORDER BY 1 is accepted syntax. It's used when you're uncertain of the column names but know you want to order by column 1 in the select list. It's a bit like using SELECT * in that regard - should really be used with caution.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Mike, the more complex plan with nested loops etc is associated with the way you've chosen to assign the query result to the variable, not to the TOP operator itself.
Try this batch, you will see what I mean:

DECLARE @MaxID INT

-- plan with 3 nodes
SELECT TOP(1) EntityID FROM dbo.Entity ORDER BY ID
SELECT TOP(1) @MaxID = EntityID FROM dbo.Entity ORDER BY EntityID

-- plan with 7 nodes
SELECT @MaxID = (SELECT TOP(1) EntityID FROM dbo.Entity ORDER BY EntityID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eric M Russell SSC Guru Points: 125522 More actions · Answer 9

Also, keep in mind that MIN and MAX are scalar operations, so they will always return a value, like a value of NULL for an empty table. However, SELECT TOP will return an empty rowset on an empty table.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

rchantler SSCrazy Points: 2128 More actions · Answer 10

rchantler

SSCrazy

Points: 2128

December 17, 2018 at 7:37 am

#2016508

Looks like query 2 has a typo.

Sean Lange SSC Guru Points: 286573 More actions · Answer 11

steve.powell 14027 - Monday, December 17, 2018 2:02 AM
denisock: Yes, ORDER BY 1 is accepted syntax. It's used when you're uncertain of the column names but know you want to order by column 1 in the select list. It's a bit like using SELECT * in that regard - should really be used with caution.

It should be avoided. It is confusing, notice the previous person here thought it was being sorted by a constant instead of the ordinal position. It ONLY works in the order by, if using partition clause you must use the column name. It is a poor coding practice. I would argue that if you don't know the names of the columns how can you be certain that you know which column to order by?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gotqn SSC Veteran Points: 277 More actions · Answer 12

steve.powell 14027 - Monday, December 17, 2018 2:02 AM
denisock: Yes, ORDER BY 1 is accepted syntax. It's used when you're uncertain of the column names but know you want to order by column 1 in the select list. It's a bit like using SELECT * in that regard - should really be used with caution.

It is accepted. In this article - it's not as the queries which are compared returns different result sets. So, there is no point to compare different queries 🙂

thisisfutile Hall of Fame Points: 3668 More actions · Answer 13

gotqn - Monday, December 17, 2018 12:03 AM
The queries are not the same. You are sorting by constant and you should order sort by the id in order to get the same results, right?

He's not going for the same queries, he's going for the same results and then trying to see what's faster/slower. To use "TOP" and guarantee the minimum or maximum of that column you must use an ORDER BY clause.

Here's Microsoft's "Best Practices" section:
In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP

HTH

thisisfutile Hall of Fame Points: 3668 More actions · Answer 14

thisisfutile - Monday, December 17, 2018 8:59 AM
gotqn - Monday, December 17, 2018 12:03 AM
The queries are not the same. You are sorting by constant and you should order sort by the id in order to get the same results, right?
He's not going for the same queries, he's going for the same results and then trying to see what's faster/slower. To use "TOP" and guarantee the minimum or maximum of that column you must use an ORDER BY clause.
Here's Microsoft's "Best Practices" section:
In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP
HTH

Oh wait, after posting this, I'm starting to think you don't understand what "order by 1" means...that simply means order by column 1.