Randomly selecting grouped records

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)

Randomly selecting grouped records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-10 : 07:57:43

Brian writes "Hello,
My question is how I can randomly select a certain amount of records that are grouped. For example...every record has an associated event date. I can have an event date of 12/01/2005 or 12/12/2005...these dates can have up to 20 in a month. For each event date there might be up to 1000 records. My goal is to randomly select 100 records for each event date. So for 12/01/2005 I want to bring in 100 records with that date...also with 12/12/2005. I know how to randomly select records but I'm not sure on how to configure a loop that selects 100 from one date and then moves onto the next 100 for another date. Any help will be greatly appreciated.
Thanks,
Brian"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-10 : 08:02:44

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

igkins
Starting Member

1 Post

Posted - 2007-10-14 : 19:15:22

Unless I'm missing something, this shows how to get the top N or bottom N from a group, but what if you need to get 10 random results back per group?

Reporter
Starting Member

48 Posts

Posted - 2007-10-16 : 09:37:59

Yes! It's solved by me, just now. The code is warm yet :)
The select and idea is very simple. And it's suitable for common case, in my opinion.

declare @tab table (RecID int identity(1,1),Start datetime,Param int) -- Param or List of Param which you need.
insert @tab
select Start,Param from TableName

select * from
(select Num=count(v2.Start), v1.Start, v1.Param
from @tab as v1 inner join @tab as v2 on v1.Start=v2.Start and v1.RecID<=v2.RecID
group by v1.Start,v1.Param,v1.RecID) as v1 where v1.Num<=100

order by 2,1

That's all. What do you think about this?

PS. Please, write me with mark "SQLTeam".
My mail is www.com@mail.ru

Andrey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 09:45:52

Where is the "random" part?

Try to use (SELECT TOP 100 somethinf order by newid())
It will not be lightning fast, but it will work.

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-10-16 : 10:01:55

Yes, surely.

declare @tab table (RecID int identity(1,1),Start datetime,Param int) -- Param or List of Param which you need.
insert @tab
select Start,Param from TableName order by newid()

But the main solution is "RecID int identity(1,1)", isn't it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources