TOP Clause

  • nenad-zivkovic

    Default port

    Points: 1446

    Comments posted to this topic are about the item TOP Clause

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

    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

    learnt something new today 🙂

    Good QOTD

  • twin.devil

    SSC-Insane

    Points: 22208

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

    Thanks for sharing

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 587

    Nice question to close the week

    Thanks for remember how to use TOP and WITH TIES

  • Ed Wagner

    SSC Guru

    Points: 286959

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

  • TomThomson

    SSC Guru

    Points: 104772

    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: 7162

    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: 5689

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


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • Tock

    Default port

    Points: 1432

    Good question and good explanation.

    Cheers,

    Steve

  • RLilj33

    SSCrazy

    Points: 2146

    Tock (5/9/2014)


    Good question and good explanation.

    +1

  • sql Sarah

    SSCommitted

    Points: 1971

    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: 12241

    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 TABLE (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;

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

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