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.
| Author |
Topic |
|
rmstock
Starting Member
1 Post |
 Posted - 2004-10-06 : 10:38:35
|
| I'm sure this should be feasible but can't quite get my head round the correct SQL...I've got a table containing records relating to user tasks and when they were completed. There are the following fields:TaskIDUserIDActivityDate CompleteWhat I want to get out of the table is a random selection of 10 individual tasks completed by each user for each available activity. The data needs to be over a specified date range. I don't have a problem getting a random list of everything in a given list by ordering my query by userID, activity and NEWID(). What I have a problem with is then reducing that random list down to 10 records per user/activity. Using TOP 10 just leaves me with 10 records total.Any suggestions gratefully received.ThanksRob Stock |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-06 : 10:49:16
|
will this do:Select TaskID, UserID, Activity, DateCompleteFrom myTable as AWhere (Select count(1) From myTable Where TaskID = A.TaskID and UserID = A.UserID DateComplete <= A.DateComplete)<=10Order By TaskID, UserID, Activity, DateCompleteplay with it.Go with the flow & have fun! Else fight the flow  |
 |
|
|
|
|
|
Selecting top set of random records