SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help selecting records with a unique column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

benricho
Yak Posting Veteran

Australia
84 Posts

Posted - 06/07/2005 :  04:22:11  Show Profile  Reply with Quote
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

India
22765 Posts

Posted - 06/07/2005 :  04:41:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Australia
84 Posts

Posted - 06/07/2005 :  20:05:44  Show Profile  Reply with Quote
Perfect, thanks Madhivanan. Makes perfect sense, don't know why I couldn't see if before.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000