"Rogas69" <rogas69@no_spamers.o2.ie> wrote in message
news:%23EaBeLqTGHA.2156@tk2msftngp13.phx.gbl...
> insert into aTable(fld1, fldRand)
>
> select top 100 fld1, rand(convert(int, convert(varbinary(4), newid())))
> from AnotherTable order by newid() --provided fld1 is int
>
> hth
>
> Peter
>
>

"Rogas69" wrote:

> actually, it doesn't matter if it's int :)
>
> "Rogas69" <rogas69@no_spamers.o2.ie> wrote in message
> news:%23EaBeLqTGHA.2156@tk2msftngp13.phx.gbl...
> > insert into aTable(fld1, fldRand)
> >
> > select top 100 fld1, rand(convert(int, convert(varbinary(4), newid())))
> > from AnotherTable order by newid() --provided fld1 is int
> >
> > hth
> >
> > Peter
> >
> >
>
>
>

"Phil Hobgen" <PhilHob***@discussions.microsoft.com> wrote in message
news:9E3222A9-99C4-425C-B89B-481C47AF97B0@microsoft.com...
> Hi,
>
> I need to select a number of rows from a query result. EG I may want to
> return only 100 rows from a query that returns 500 rows. The rows returned
> must be distributed across the query result set. If the same query is run
> twice then a different 100 rows should be returned each time.
>
> I have put an extra column in the table I am selecting from and I try to
> populate this with a random number, then to get the distribution I order
> by
> the random column and take the TOP N rows  - however when populating the
> random column if I do a command such as:
> INSERT INTO Table (fld1, fldRand)
>       (SELECT fld1, fldRand = RAND() FROM AnotherTable WHERE some
> condition)
>
> then all rows end up with the same fldRand value; it seems that RAND() is
> only called once. If I change it to RAND(SomeColumn) then it gets called
> for
> each row however this doesn't help me as the next time the same query is
> run
> the same values will be generated into the random column and so the
> distribution order will be the same.
>
> Is there some way I can force the RAND() function to be called for each
> row?
> Or should I approach this some other way?
>
> Many thanks
>
>
>
> --
> Phil Hobgen
> <a href="http://www.barbari.co.uk/default.aspx"
> target="_blank">barbari</a>
> Southampton, UK

"Raymond D'Anjou" wrote:

> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message
> news:OlHPO9qTGHA.5900@tk2msftngp13.phx.gbl...
> > Without creating that extra column, wouldn't:
> > select ... from table ORDER BY NEWID()
> > work for you.
>
> Sorry:
> select top 100 ... from table ORDER BY NEWID()
>
>
>