On 6 19 , 9 52 , Andrew <And...@discussions.microsoft.com> wrote:
> Hello, friends,
>
> I am not that good in SQL programming, so please help.
>
> I need to randomly select just one record from unknown number records that
> belong to the same group, say grouped by CategoryID.
>
> I know I can do it in C#.net like the follows:
>
> Query SQL Server for the toal number of records for a given CategoryID;
> Generate a random number in C#;
> Retrieve the record by its index number (i.e. the random number generated
> above);
>
> This works, but has very big disavantage: All records have to be transfered
> to client machine from db server, no matter how many, say 10,000.
>
> I am thinking if a good SQL programed stored procedure can make only the
> randomly selected record be returned from db to client, rather than ALL
> records.
>
> Any snippet or reference papers? Thanks a lot for your help!

"xyb" wrote:

> On 6 19 , 9 52 , Andrew <And...@discussions.microsoft.com> wrote:
> > Hello, friends,
> >
> > I am not that good in SQL programming, so please help.
> >
> > I need to randomly select just one record from unknown number records that
> > belong to the same group, say grouped by CategoryID.
> >
> > I know I can do it in C#.net like the follows:
> >
> > Query SQL Server for the toal number of records for a given CategoryID;
> > Generate a random number in C#;
> > Retrieve the record by its index number (i.e. the random number generated
> > above);
> >
> > This works, but has very big disavantage: All records have to be transfered
> > to client machine from db server, no matter how many, say 10,000.
> >
> > I am thinking if a good SQL programed stored procedure can make only the
> > randomly selected record be returned from db to client, rather than ALL
> > records.
> >
> > Any snippet or reference papers? Thanks a lot for your help!
> I hope this sample code can help you.
> create table #t
> (
> pid int
> )
>
> declare @i int
> select @i= 100
> while @i > 0
> begin
> insert into #t
> select rand()*100
> select @i = @i - 1
> end
>
> select * from #t
>
> with cte as
> (
> select pid,
> row_number() over (order by pid) as rnt
> from #t
> )
> select top 1 * from cte
> where
> rnt > rand()*100
>