TOP vs Max/Min: Is there a difference?

  • Mike Byrd

    Ten Centuries

    Points: 1303

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

    Mike Byrd

  • Lynn Pettis

    SSC Guru

    Points: 442345

    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

    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: 267

    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

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

  • David.Poole

    SSC Guru

    Points: 75380

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

  • steve.powell 14027

    SSC Veteran

    Points: 243

    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: 243

    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: 186107

    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)

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Eric M Russell

    SSC Guru

    Points: 125096

    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: 2125

    Looks like query 2 has a typo.

  • Sean Lange

    SSC Guru

    Points: 286536

    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: 267

    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: 3527

    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: 3527

    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.

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

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