Professional Web Applications Themes

Top N rows - Microsoft SQL / MS SQL Server

How do you get the top N rows from a query where N is any number? I know I can do something like this: Select Top 100 ColA From Table1 but is there any way to do: Select Top N ColA From Table1 and assign N at runtime in some kind of stored procedure?...

  1. #1

    Default Top N rows

    How do you get the top N rows from a query where N is any
    number? I know I can do something like this:

    Select Top 100 ColA
    From Table1

    but is there any way to do:

    Select Top N ColA
    From Table1

    and assign N at runtime in some kind of stored procedure?
    Scott Guest

  2. #2

    Default Re: Top N rows

    TOP cannot accept a variable. One workaround is:

    SET ROWCOUNT n
    SELECT ColA FROM Table1
    SET ROWCOUNT 0

    Note that this is slightly less efficient than TOP n, since the "top" is
    applied to the full resultset. To gain a little bit of efficiency back, one
    potential mod is, if you have a maximum for n, and if you are using a TOP
    that is meaningful (e.g. with an ORDER BY clause, otherwise you don't really
    have a context for "top"), you could do this:

    -- let's say MAX(n) = 200
    SET ROWCOUNT n
    SELECT ColA FROM Table1
    ORDER BY <order by?>
    OPTION (FAST 200)
    SET ROWCOUNT 0






    "Scott Good" <com> wrote in message
    news:01bc01c368d8$f723f7a0$gbl... 


    Aaron Guest

  3. #3

    Default Re: Top N rows

    Check SET ROWCOUNT



    "Scott Good" <com> wrote in message
    news:01bc01c368d8$f723f7a0$gbl... 


    Ivan Guest

  4. #4

    Default Re: Top N rows

    > What does OPTION(FAST 200) do?

    It tells the optimizer to focus its efforts on the first 200 rows when
    generating the plan. There is a bit of information at the bottom of this
    article:

    http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/tuningofadifferentsort.asp
     

    You'll have to tell us after *testing* it. How much faster it is, if it is
    faster at all, depends on far too many factors to even list, never mind
    yze and try to give you a realistic answer.


    Aaron Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. Replies: 3
    Last Post: September 20th, 12:38 PM
  3. add rows
    By Mike in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 10th, 09:28 PM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139