TOP and ORDER BY

  • yogyogi97

    SSC Veteran

    Points: 248

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

  • Srinivas.Pendyala

    SSC Enthusiast

    Points: 119

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

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Srinivas.Pendyala

    SSC Enthusiast

    Points: 119

    thanks yogesh,

    now i can undestand.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Good Explanation .. Lokesh

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Lokesh Vij (6/3/2013)


    Easy one for the day. Thanks Yogesh:-)

    +1:-)

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

  • manik_anu

    SSCrazy

    Points: 2367

    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.

  • Jamsheer

    Ten Centuries

    Points: 1136

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

  • This was removed by the editor as SPAM

  • Kevin Gill

    SSCrazy

    Points: 2240

    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!

  • Revenant

    SSC-Forever

    Points: 42467

    Nice back-to-basics - thanks, Yogesh!

  • Michael Poppers

    SSCrazy

    Points: 2120

    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