Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
benricho
Yak Posting Veteran
84 Posts |
Posted - 2005-06-07 : 04:22:11
|
I had a hard time coming up with a good subject for this. I think the best way for me to describe the problem is to jump straight into the DDL.CREATE TABLE AnimalType ( [ID] int primary key, [Name] varchar(20))GOINSERT INTO AnimalType VALUES (1, 'Dog')GOINSERT INTO AnimalType VALUES (2, 'Cat')GOCREATE TABLE Staff ( [ID] int primary key, [Name] varchar(20), AnimalPreference int FOREIGN KEY REFERENCES AnimalType([ID]))GOINSERT INTO Staff VALUES (1, 'Ben', 1)GOINSERT INTO Staff VALUES (2, 'Steve', 1)GOINSERT INTO Staff VALUES (3, 'Greg', 1)GOINSERT INTO Staff VALUES (4, 'Dave', 2)GOIn the results I want random staff members, but only 1 for each different AnimalType.So possible results could be1, Ben, 14, Dave, 2Or3, Greg, 14, Dave, 2The only way I can think of doing this would be to use a cursor to get the unique animal types, then loop through selecting random staff (using order by newid()), and adding these staff to a temp table, and selecting the entire temp table to get the results.But I get the feeling that there is a better way to do this that I just can't see. Any ideas? |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-07 : 04:41:30
|
Try thisselect distinct animalpreference,(select top 1 id from staff where animalpreference=s.animalpreference order by newid())from staff s MadhivananFailing to plan is Planning to fail |
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2005-06-07 : 20:05:44
|
Perfect, thanks Madhivanan. Makes perfect sense, don't know why I couldn't see if before. |
|
|
|
|
|
|
|