Professional Web Applications Themes

Urgent syntax question - Microsoft SQL / MS SQL Server

I have a recordset with approx. 189,000 records in it. I need to do an ysis based on random groupings of the data. For instance, on random 60% of the records within a certain criteria, I want to apply one part of the ysis, and for the remaining 40% another part of the ysis. Is there a way to choose a percentage of random records for querying? Thanks in advance for your help. Deborah...

  1. #1

    Default Urgent syntax question

    I have a recordset with approx. 189,000 records in it. I need to do an
    ysis based on random groupings of the data. For instance, on random 60%
    of the records within a certain criteria, I want to apply one part of the
    ysis, and for the remaining 40% another part of the ysis.

    Is there a way to choose a percentage of random records for querying?

    Thanks in advance for your help.

    Deborah


    Deborah Bohannon Guest

  2. #2

    Default Re: Urgent syntax question

    I am sure there are lots of ways but one rather easy way is to add a column
    and fill it with a randomly generated number. Then use that with the TOP 60
    PERCENT to get the rows you want.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Deborah Bohannon" <dbohannonnationalcarenetwork.com> wrote in message
    news:OIRMw4WRDHA.2332TK2MSFTNGP10.phx.gbl...
    > I have a recordset with approx. 189,000 records in it. I need to do an
    > ysis based on random groupings of the data. For instance, on random
    60%
    > of the records within a certain criteria, I want to apply one part of the
    > ysis, and for the remaining 40% another part of the ysis.
    >
    > Is there a way to choose a percentage of random records for querying?
    >
    > Thanks in advance for your help.
    >
    > Deborah
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: Urgent syntax question

    I've never used this function before. I checked BOL and they have one
    example of how this could be used, but the result it gives is somewhat
    different I think than what I want. It produces numbers that are not whole
    numbers.

    This is what BOL says:

    DECLARE counter smallint
    SET counter = 1
    WHILE counter < 5
    BEGIN
    SELECT RAND(counter) Random_Number
    SET NOCOUNT ON
    SET counter = counter + 1
    SET NOCOUNT OFF
    END
    GOHere is the result set:Random_Number
    -------------------
    0.71359199321292355

    (1 row(s) affected)

    Random_Number
    -------------------
    0.7136106261841817

    (1 row(s) affected)

    Random_Number
    -------------------
    0.71362925915543995

    (1 row(s) affected)

    Random_Number
    -------------------
    0.7136478921266981

    (1 row(s) affected)

    In order to get the numbers I need, is this how I would change the
    query:DECLARE counter int
    SET counter = 1
    WHILE counter < 187990
    BEGIN
    SELECT RAND(counter) Random_Number
    SET NOCOUNT ON
    SET counter = counter + 1
    SET NOCOUNT OFF
    END
    GO
    Any insight is appreciated.Deborah"Andrew J. Kelly"
    <sqlmvpnooospamshadhawk.com> wrote in message
    news:O$aswJXRDHA.2148TK2MSFTNGP11.phx.gbl...
    > I am sure there are lots of ways but one rather easy way is to add a
    column
    > and fill it with a randomly generated number. Then use that with the TOP
    60
    > PERCENT to get the rows you want.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Deborah Bohannon" <dbohannonnationalcarenetwork.com> wrote in message
    > news:OIRMw4WRDHA.2332TK2MSFTNGP10.phx.gbl...
    > > I have a recordset with approx. 189,000 records in it. I need to do an
    > > ysis based on random groupings of the data. For instance, on random
    > 60%
    > > of the records within a certain criteria, I want to apply one part of
    the
    > > ysis, and for the remaining 40% another part of the ysis.
    > >
    > > Is there a way to choose a percentage of random records for querying?
    > >
    > > Thanks in advance for your help.
    > >
    > > Deborah
    > >
    > >
    >
    >

    Deborah Bohannon Guest

  4. #4

    Default Re: Urgent syntax question

    > Is there a way to choose a percentage of random records for querying?

    Sure, you could try

    SELECT TOP 60 PERCENT column_list FROM table_name ORDER BY NEWID()

    Now, it might be a little difficult to determine, for your second query, the
    40% that are left. :-)


    Aaron Bertrand - MVP Guest

Similar Threads

  1. Question about E4X syntax.
    By rpierich in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: May 27th, 06:12 AM
  2. Simple syntax question
    By Hompf in forum MySQL
    Replies: 2
    Last Post: March 10th, 09:32 PM
  3. Question on syntax...
    By Jonathan Villa in forum PHP Development
    Replies: 1
    Last Post: August 20th, 03:15 PM
  4. Question on class syntax
    By Greg Beaver in forum PHP Development
    Replies: 0
    Last Post: August 7th, 06:34 PM
  5. SQL Syntax Question???
    By Kory in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 10th, 10:57 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