TOP Clause

  • 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

  • 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

  • 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.

  • learnt something new today 🙂

    Good QOTD

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

    Thanks for sharing

  • Nice question to close the week

    Thanks for remember how to use TOP and WITH TIES

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

  • 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

  • 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
    🙂

  • 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]

  • Good question and good explanation.

    Cheers,

    Steve

  • Tock (5/9/2014)


    Good question and good explanation.

    +1

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

  • 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