Complicated (maybe)

Server Type
-- --
S1 A
S2 A
S3 B
S4 B
S5 C
S6-... D
I need to randomly pick one server that is type A, one that is type B,
one that is type C, and 5 that are type D (Of course there is more than
2 type A's, etc than the example).
Is there a good way to do it thru SQL? Currently I am considering
breaking them out to different views, randomly selecting from those,
then merging the results. But I was wondering if there was an easier
way?
Thanks.d4 (d4mann@.gmail.com) writes:
> Server Type
> -- --
> S1 A
> S2 A
> S3 B
> S4 B
> S5 C
> S6-... D
> I need to randomly pick one server that is type A, one that is type B,
> one that is type C, and 5 that are type D (Of course there is more than
> 2 type A's, etc than the example).
> Is there a good way to do it thru SQL? Currently I am considering
> breaking them out to different views, randomly selecting from those,
> then merging the results. But I was wondering if there was an easier
> way?
Different views seems a bit overkill, but you would need four different
queries:
SELECT TOP 1 Server FROM Servers WHERE Type = 'A' ORDER BY NEWID()
You could of course insert these into a temp table, so that the client
only has to handle one result set.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The "random selection" approaches in SQL are more or less non-intuitive. One
option in t-SQL is to do:
SELECT t1.Type,
( SELECT TOP 1 t2.Server
FROM tbl t2 WHERE t2.Type = t1.Type
ORDER BY NEWID() )
FROM tbl t1
GROUP BY t1.Type ;
If the server value need not be "random", you can use MIN or MAX directly in
your query.
Anith|||Try this:
set nocount on
create table #servers (server char(2), type char(1))
insert #servers select 'S1', 'A'
insert #servers select 'S2', 'A'
insert #servers select 'S3', 'B'
insert #servers select 'S4', 'B'
insert #servers select 'S5', 'C'
insert #servers select 'S6', 'D'
select (select top 1 server from #servers where type = S.type order by
newid()) from #servers S group by S.type
drop table #servers
Erland, do you see a problem with this?
"d4" <d4mann@.gmail.com> wrote in message
news:1132151625.063400.312760@.z14g2000cwz.googlegroups.com...
> Server Type
> -- --
> S1 A
> S2 A
> S3 B
> S4 B
> S5 C
> S6-... D
> I need to randomly pick one server that is type A, one that is type B,
> one that is type C, and 5 that are type D (Of course there is more than
> 2 type A's, etc than the example).
> Is there a good way to do it thru SQL? Currently I am considering
> breaking them out to different views, randomly selecting from those,
> then merging the results. But I was wondering if there was an easier
> way?
> Thanks.
>|||Raymond D'Anjou (rdanjou@.canatradeNOSPAM.com) writes:
> set nocount on
> create table #servers (server char(2), type char(1))
> insert #servers select 'S1', 'A'
> insert #servers select 'S2', 'A'
> insert #servers select 'S3', 'B'
> insert #servers select 'S4', 'B'
> insert #servers select 'S5', 'C'
> insert #servers select 'S6', 'D'
> select (select top 1 server from #servers where type = S.type order by
> newid()) from #servers S group by S.type
> drop table #servers
> Erland, do you see a problem with this?
Yes, it does not meet the requirements:
===
If D is he only he wants more than one of, he could do:
select (select top 1 server from #servers where type = S.type order by
newid()) from #servers S where S.type <> 'D' group by S.type
union all
select top 5 server from #servers where type = 'D' order by newid()
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx