Professional Web Applications Themes

select a number of rows in a table - Microsoft SQL / MS SQL Server

I assume that exists a field named id in the table... Select top 3 id from Table where id not in (select top 6 id from Table) 3 = 9-7+1 6 = 7-1 In general, for select rows from x to y: Select top y-x+1 id from Table where id not in (select top x-1 id from Table) if you want rows x and y to be included... or Select top y-x id from Table where id not in (select top x- 1 id from Table) if you want row x not to be included and y to be included... ...

  1. #1

    Default select a number of rows in a table

    I assume that exists a field named id in the table...

    Select top 3 id from Table where id not in (select top 6
    id from Table)

    3 = 9-7+1
    6 = 7-1

    In general, for select rows from x to y:
    Select top y-x+1 id from Table where id not in (select top
    x-1 id from Table)

    if you want rows x and y to be included...

    or

    Select top y-x id from Table where id not in (select top x-
    1 id from Table)

    if you want row x not to be included and y to be
    included...

    or

    Select top y-x-1 id from Table where id not in (select top
    x-1 id from Table)

    if you want rows x and y not to be included...

    If you don't have id , any field which uniquely identifies
    a row is ok.


     
    Lucian Guest

  2. #2

    Default Re: select a number of rows in a table

    You always have to include ORDER BY with TOP if you want a consistent,
    meaningful result:

    SELECT TOP 3 id
    FROM [table]
    WHERE id NOT IN
    (SELECT TOP 6 id
    FROM [table]
    ORDER BY id)
    ORDER BY id

    At least you got the numbers right, unlike me :-)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Lucian Secara" <ro> wrote in message
    news:04cc01c3494c$ae333700$gbl... [/ref]


    David Guest

Similar Threads

  1. Replies: 8
    Last Post: March 16th, 09:20 AM
  2. How to display limited number of rows of table indatabase
    By cgacfox in forum Coldfusion Database Access
    Replies: 12
    Last Post: March 10th, 08:54 PM
  3. Replies: 0
    Last Post: September 15th, 05:39 AM
  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
  5. Select Intermittently Returning Incorrect Number of Rows
    By Karsten Farrell in forum Oracle Server
    Replies: 0
    Last Post: January 13th, 06:48 PM

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