Select 10 contacts in a company randomly

Question

Post reply

Select 10 contacts in a company randomly

sharonrao123

Default port

Points: 1450
More actions
November 29, 2009 at 6:32 pm

#137164

hi All,
We have two table company and people and people has coid as a foreign key. I need to select 10 contacts or less for each company randomly.
Below are the structures:
Company - CoID, Company, address, street, city, state, postcode, country, phone, fax, addedby, addedon, editedby, editedon
People- PersonID, CoID, Firstname, lastname, title, persontype, email, addedon, addedby, editedon, editedby
Thanks for your help, please get me started

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

This should get you started...

WITH

cteRandomlyNumber AS

(

SELECT PersonID,

CoID,

RowNum = ROW_NUMBER() OVER (PARTITION BY CoID ORDER BY NEWID())

FROM dbo.People

)

SELECT PersonID, CoID

FROM cteRandomlyNumber

WHERE RowNum <= 10

Takes about 12 seconds on a million employees across 676 different companies.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharonrao123 Default port Points: 1450 More actions · Answer 2

hi,

You are a star it worked like a charm, thanks for your time.

-Shilpa.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Thanks for the feedback, Shilpa.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 4

I thought that GUIDs didn't have an order. I am never able to ORDER by a GUID, so why are you able to ORDER BY NEWID() here?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

To produce a random order. It's an old trick.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)