Random sample of 5 records per user

[dddenney]

I need to figure out a way to pull a random sample of 5 calls per day per user.

TableA (ID INT, UserID INT, CallDate DATETIME, CallDesc VARCHAR(100))


So far:

select userid, callDesc
from TableA
where callDate between yesterday and today
order by NEWID()

But how can I pull just 5 recs per user? I need all users with calls to show up.



Thanks in Advance,
Dan

 

[RyanEK]

Hi,

Not tested. Let me know if it works...

select t1.UserId, t1.ID
from [Table] t1
group by t1.UserId, t1.ID
having t1.ID in (
	SELECT TOP 3 t2.ID
	FROM [Table] t2
	where t2.UserId= t1.UserId
	order by newid()
)
order by t1.UserId, t1.ID

Ryan