TOP vs Max/Min: Is there a difference?

  • Mike Byrd

    Ten Centuries

    Points: 1285

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    Mike Byrd

  • Eirikur Eiriksson

    SSC Guru

    Points: 182411

    Mike Byrd - Monday, December 17, 2018 9:47 AM

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    The methods are less important than the underlying structure, statistics and cardinality. I've tested this in the passed and found that when the structure is correctly optimized, there is hardly any difference. In an un-optimized situation, the difference can potentially be huge, but then again, that's like teaching someone to swim with concrete boots!
    😎

  • Mike C

    SSC-Insane

    Points: 23224

    Mike Byrd - Monday, December 17, 2018 9:47 AM

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182411

    Mike C - Monday, December 17, 2018 10:09 AM

    Mike Byrd - Monday, December 17, 2018 9:47 AM

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.

    Order by # is a subject to deprecation, do not use it!
    😎

  • gotqn

    SSC Veteran

    Points: 267

    thisisfutile - Monday, December 17, 2018 9:01 AM

    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.

    :):D
    True, I was so sure that he is ordering by constant like in window function when one is using `SELECT 1`. For example ROW_NUMBER OVER (ORDER BY (SELECT 1)) ...
    Just forgot for a moment this legacy ordering by using the number of the column instead the alias ... xax

  • Mike C

    SSC-Insane

    Points: 23224

    Eirikur Eiriksson - Monday, December 17, 2018 10:15 AM

    Mike C - Monday, December 17, 2018 10:09 AM

    Mike Byrd - Monday, December 17, 2018 9:47 AM

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    Any time you invoke nonstandard syntax, you can probably expect a fire-storm. The ordinal position reference syntax in the ORDER BY clause was last seen in the ANSI SQL-92 standard and was removed in ISO 9075:1999. Microsoft has kept it around, but I wouldn't rely on it.

    Order by # is a subject to deprecation, do not use it!
    😎

    I haven't seen it on a deprecation list yet, but I agree they will probably get rid of it at some point.

  • neil-560592

    SSCommitted

    Points: 1557

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    http://90.212.51.111 domain

  • Sean Lange

    SSC Guru

    Points: 286518

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    _______________________________________________________________

    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/

  • Jeff Moden

    SSC Guru

    Points: 995703

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sean Lange

    SSC Guru

    Points: 286518

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉

    _______________________________________________________________

    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/

  • Lynn Pettis

    SSC Guru

    Points: 442245

    Sean Lange - Tuesday, December 18, 2018 1:18 PM

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉

    I can actually say that I have never used ORDER BY ordinal position in any code I have written.
    If I want to order by a column or set of columns I will explicitly specify the column(s).

  • rchantler

    SSCrazy

    Points: 2116

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    Does deprecation ever happen?  I thought SQL collations were going to be deprecated years ago.  (maybe they have been??) in favor of Windows collations.

  • Sean Lange

    SSC Guru

    Points: 286518

    Lynn Pettis - Tuesday, December 18, 2018 2:09 PM

    Sean Lange - Tuesday, December 18, 2018 1:18 PM

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    I have never seen it on an official deprecation list. In fact as a result of this thread I was digging and still don't see it. I agree that it is useful for development from time to time but not something I really use very often. I usually think in my head that I want to order by a column, then oh it doesn't matter which column so before I have a chance to think of using 1 I have already typed in the primary key of the main table in the query. 😉

    I can actually say that I have never used ORDER BY ordinal position in any code I have written.
    If I want to order by a column or set of columns I will explicitly specify the column(s).

    Same here. When I see the ordinal position I have to stop and think about it not being a constant (meaning inconsistent order).

    _______________________________________________________________

    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/

  • Sean Lange

    SSC Guru

    Points: 286518

    rchantler - Tuesday, December 18, 2018 2:16 PM

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    Does deprecation ever happen?  I thought SQL collations were going to be deprecated years ago.  (maybe they have been??) in favor of Windows collations.

    Sure stuff gets deprecated every release. Sadly most of them get removed, which they should. I have never heard that sql collations would be deprecated. Where did you read that? I highly doubt that would happen anyway. SQL Server now runs on Linux. If they used on Windows collations instead of their own that would prove to be nearly impossible.

    _______________________________________________________________

    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/

  • Jeff Moden

    SSC Guru

    Points: 995703

    Mike Byrd - Monday, December 17, 2018 9:47 AM

    Wow, didn't know the article was going to generate a fire-storm on TSQL syntax.  The original purpose was to see if there was a difference getting max/min value of a column using the TOP or MAX/MIN functions.  I agree that ORDER BY 1 (first column in result set) should not be used in production -- but that was not the purpose of the article.  What I didn't anticipate is all the other query plan operators needed to satisfy the overall particular query syntax.  Normally I am very strict in my TSQL syntax for going into production, but obviously more lax in ad hoc queries.  Thanks everyone for your replies.

    If you're article starts a fire storm or any other type of fire, it was worth the time. 

    Shifting gears a bit, could you post the CREATE TABLE code for the table you used along with the indexes?  I'd like to play a bit.

    Also, just a tip from a craggy ol' writer... posting code as a graphic is fine but only if you include the code as a file in the resource section of the article.  That way people can just copy'n'paste your code if they want to "Sing along with Mitch".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 34 total)

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