Random Records Selection - SQL Server Forums

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

SQL Server Development (2000)

Random Records Selection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-02 : 07:43:20

Kym writes "I have a table that contain many rows and a few columns. Within the table there are various instances that have a 1:Many relationship and I am interested in extracting a random sample of approximately 5 records from each 1:Many scenario. For example there are 1,300 rules with several records associated with each rule. I am interested in selecting 5 records from each rule ID. Can you please assist?

I am using version 8.00.194

Thank you,
Kym"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-02 : 08:29:06

I think it can be done with Cursor and Top operator

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-02 : 08:40:40

well this is one way:


use northwind

Select *
From orders as t1
Where (Select count(distinct orderdate) from orders Where customerid = t1.customerid and orderdate >= t1.orderdate)<=5
Order By newid()

for something better we'd need some sample data and desired results along with table definitions.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources