Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select 10 contacts in a company randomly Expand / Collapse
Author
Message
Posted Sunday, November 29, 2009 6:32 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 15, 2015 5:32 PM
Points: 66, Visits: 205
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
Post #825965
Posted Sunday, November 29, 2009 8:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 PM
Points: 40,378, Visits: 37,587
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #825978
Posted Sunday, November 29, 2009 9:13 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 15, 2015 5:32 PM
Points: 66, Visits: 205
hi,

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

-Shilpa.
Post #825980
Posted Sunday, November 29, 2009 9:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 PM
Points: 40,378, Visits: 37,587
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #825984
Posted Monday, November 30, 2009 9:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 19, 2015 2:43 PM
Points: 67, Visits: 231
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?
Post #826319
Posted Monday, November 30, 2009 10:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 PM
Points: 40,378, Visits: 37,587
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #826376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse