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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help selecting records with a unique column

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)
)
GO

INSERT INTO AnimalType VALUES (1, 'Dog')
GO
INSERT INTO AnimalType VALUES (2, 'Cat')
GO

CREATE TABLE Staff (
[ID] int primary key,
[Name] varchar(20),
AnimalPreference int FOREIGN KEY REFERENCES AnimalType([ID])
)
GO

INSERT INTO Staff VALUES (1, 'Ben', 1)
GO
INSERT INTO Staff VALUES (2, 'Steve', 1)
GO
INSERT INTO Staff VALUES (3, 'Greg', 1)
GO
INSERT INTO Staff VALUES (4, 'Dave', 2)
GO

In the results I want random staff members, but only 1 for each different AnimalType.

So possible results could be

1, Ben, 1
4, Dave, 2

Or

3, Greg, 1
4, Dave, 2

The 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 this

select distinct animalpreference,(select top 1 id from staff where animalpreference=s.animalpreference order by newid())
from staff s



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -