TOP Clause

Question

TOP Clause

nenad-zivkovic

Default port

Points: 1448
More actions
May 8, 2014 at 11:49 pm

#286596

Comments posted to this topic are about the item TOP Clause
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
May 9, 2014 at 1:40 am

This was removed by the editor as SPAM

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Very interesting question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Thank you for the post, good on, memory restacked.

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

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 3

pmadhavapeddi22

SSCertifiable

Points: 5306

May 9, 2014 at 4:12 am

#1712099

learnt something new today 🙂

Good QOTD

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Nice question ... brought back some nice old memories ... 🙂

Thanks for sharing

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 5

Nice question to close the week

Thanks for remember how to use TOP and WITH TIES

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

A good question to end the week. Thanks for the reminder about WITH TIES.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Nice fun question. 76% correct so far, which means people have found it easier than most QOTD. Perhaps it would have been even easier with a different code layout.

edit: I can get typos even in something this short.

Tom

rhythmk SSCertifiable Points: 7271 More actions · Answer 8

Nice question.Thanks 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

david.gugg SSCertifiable Points: 6096 More actions · Answer 9

Good question, I hadn't heard of WITH TIES before. Thanks!

Be still, and know that I am God - Psalm 46:10

Tock Default port Points: 1433 More actions · Answer 10

Good question and good explanation.

Cheers,

Steve

RLilj33 SSCrazy Points: 2153 More actions · Answer 11

Tock (5/9/2014)
Good question and good explanation.

+1

sql Sarah SSCrazy Points: 2081 More actions · Answer 12

I changed A and B to temp tables and ran this. It returned 5 rows.

How do you get 8 rows for the answer? :unsure:

sknox SSChampion Points: 12388 More actions · Answer 13

sql Sarah (5/9/2014)
I changed A and B to temp tables and ran this. It returned 5 rows.
How do you get 8 rows for the answer? :unsure:

TOP n WITH TIES will return the first n rows, PLUS any additional rows that tie for nth place. In this case, n=5, and in table B ordered by column Y, the fifth row is Y=5. There are 3 additional rows where Y=5, which makes 8 rows total.

I've tested the code below (direct copy/paste from the QotD with the minimal changes to make temporary tables or table variables) and it returns 8 rows, either way. Was your code different?

--Temporary Tables

CREATE TABLE #A (X INT);

INSERT INTO #A VALUES (1),(2),(3),(4);

CREATE TABLE #B (Y INT);

INSERT INTO #B VALUES (1),(2),(3),(4),(5),(5),(5),(5);

--How many rows are returned by the following statement?

SELECT TOP ( ( SELECT COUNT(*)

FROM #A

) + 1 ) WITH TIES *

FROM #B

ORDER BY Y;

--Table Variables

DECLARE @a TABLE (X INT);

INSERT INTO @a VALUES (1),(2),(3),(4);

DECLARE @b-2 TABLE (Y INT);

INSERT INTO @b-2 VALUES (1),(2),(3),(4),(5),(5),(5),(5);

--How many rows are returned by the following statement?

SELECT TOP ( ( SELECT COUNT(*)

FROM @a

) + 1 ) WITH TIES *

FROM @b-2

ORDER BY Y;