Return 3 random records for each supervisor 1

[NewbieWithSQL]

I have seen a lot of posts about returning top 3 random records from the entire table but couldn't find one about random records for a specific reason.

I have a table that holds information on employees and the supervisor they are assigned to. I need to find a way of getting 3 or less random employees for each supervisor that exists in the table. Reason I say 3 or less is due to some supervisors may only have 1 or 2 employees assigned to them. Any help with either the code or a link that shows what I'm trying to accomplish is greatly appreciated. I can provide dummy data if needed.

Thanks in advance

 

[djj55]

What version are you one?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[NewbieWithSQL]

SQL 2008

 

[markros]

Try

;with cte as (select *, 
row_number() over (partition by Supervisor order by NewID()) as Row
from EmployeeInfo)

select * from cte where Row <=3

I think we discussed a similar problem in MSDN forum a while back and got ingenious solution posted by Alejandro Mesa with the help of Itzik Ben-Gan. I'll try to search for this thread as well.



PluralSight Learning Library

 

[markros]

Hooray, I found that thread

http://social.msdn.microsoft.com/Fo...l/thread/32674d22-3e42-4361-bbcc-5149eb619886

Will save it now also, as it has very interesting idea.

PluralSight Learning Library

 

[NewbieWithSQL]

Thanks markros. Exactly what I need.