Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 35,609, Visits: 32,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:23 PM
Points: 44, Visits: 187
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 35,609, Visits: 32,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 35,609, Visits: 32,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Add to briefcase

Permissions Expand / Collapse