TOP and ORDER BY

  • Comments posted to this topic are about the item TOP and ORDER BY

  • what about the 5 th value,why it is not giving output of 5?

  • Easy one for the day. Thanks Yogesh:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Srinivas.Pendyala (6/3/2013)


    what about the 5 th value,why it is not giving output of 5?

    Srinivas, The idea here is we are using "TOP(6) WITH TIES", initially "TOP 6" is executed, this returns result set : {1,2,3,3,4,4}. Now because "WITH TIES" clause is present, additional rows with matching values in the result set will be returned. So we see that we shall get 7th row as the column value 4, is part of the existing result set. So the final result set is : {1,2,3,3,4,4,4}.

    Now 5 being not appearing in the result set, it shall not be returned.

    Hope it is clear now!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • thanks yogesh,

    now i can undestand.

  • Good one, thank you for the post.

    (even though knowing the answer accidently selected the wrong one and forgot the TOP clause)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Good Explanation .. Lokesh

  • Lokesh Vij (6/3/2013)


    Srinivas.Pendyala (6/3/2013)


    what about the 5 th value,why it is not giving output of 5?

    Srinivas, The idea here is we are using "TOP(6) WITH TIES", initially "TOP 6" is executed, this returns result set : {1,2,3,3,4,4}. Now because "WITH TIES" clause is present, additional rows with matching values in the result set will be returned. So we see that we shall get 7th row as the column value 4, is part of the existing result set. So the final result set is : {1,2,3,3,4,4,4}.

    Now 5 being not appearing in the result set, it shall not be returned.

    Hope it is clear now!

    +1 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Lokesh Vij (6/3/2013)


    Easy one for the day. Thanks Yogesh:-)

    +1:-)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Lokesh Vij (6/3/2013)


    Srinivas.Pendyala (6/3/2013)


    what about the 5 th value,why it is not giving output of 5?

    Srinivas, The idea here is we are using "TOP(6) WITH TIES", initially "TOP 6" is executed, this returns result set : {1,2,3,3,4,4}. Now because "WITH TIES" clause is present, additional rows with matching values in the result set will be returned. So we see that we shall get 7th row as the column value 4, is part of the existing result set. So the final result set is : {1,2,3,3,4,4,4}.

    Now 5 being not appearing in the result set, it shall not be returned.

    Hope it is clear now!

    +1 🙂 nice one....

    Manik
    You cannot get to the top by sitting on your bottom.

  • Nice question. Helped me to refresh ' WITH TIES'.. Thanks

  • This was removed by the editor as SPAM

  • Darn it I counted the 4's wrong after having a lightbulb moment about what 'With Ties' does. As I clicked the answer I knew it was wrong because it was also the most obvious one...

    🙂

    -------------------------------
    Oh no!

  • Nice back-to-basics - thanks, Yogesh!

  • kapil_kk (6/3/2013)


    Lokesh Vij (6/3/2013)


    Srinivas.Pendyala (6/3/2013)


    what about the 5 th value,why it is not giving output of 5?

    Srinivas, The idea here is we are using "TOP(6) WITH TIES", initially "TOP 6" is executed, this returns result set : {1,2,3,3,4,4}. Now because "WITH TIES" clause is present, additional rows with matching values in the result set will be returned. So we see that we shall get 7th row as the column value 4, is part of the existing result set. So the final result set is : {1,2,3,3,4,4,4}.

    Now 5 being not appearing in the result set, it shall not be returned.

    Hope it is clear now!

    +1 🙂

    Ditto :-D...and may I add that I had not seen WITH TIES before until a recent QotD, so my experience with that QotD made this QotD relatively easy for me and was a nice refresher :-).

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

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