TOP and ORDER BY

Question

Post reply

TOP and ORDER BY

yogyogi97

SSC Veteran

Points: 248
More actions
June 3, 2013 at 10:28 pm

#278136

Comments posted to this topic are about the item TOP and ORDER BY
June 4, 2013 at 6:39 am

This was removed by the editor as SPAM

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

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

Srinivas.Pendyala SSC Enthusiast Points: 119 More actions · Answer 1

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

Srinivas.Pendyala

SSC Enthusiast

Points: 119

June 3, 2013 at 11:05 pm

#1620776

thanks yogesh,

now i can undestand.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 5

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 More actions · Answer 6

PRAMANA.DBA

SSCertifiable

Points: 5507

June 3, 2013 at 11:14 pm

#1620778

Good Explanation .. Lokesh

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

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/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 8

Lokesh Vij (6/3/2013)
Easy one for the day. Thanks Yogesh:-)

+1:-)

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

manik_anu SSCrazy Points: 2377 More actions · Answer 9

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 More actions · Answer 10

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

Kevin Gill SSCrazy Points: 2348 More actions · Answer 11

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 More actions · Answer 12

Revenant

SSC-Forever

Points: 42467

June 4, 2013 at 9:33 am

#1621024

Nice back-to-basics - thanks, Yogesh!

Michael Poppers SSCrazy Points: 2149 More actions · Answer 13

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 :-).