Home > Tips N Tricks > Top N Random records from each group

Top N Random records from each group

October 12th, 2009 Arun Mallick Leave a comment Go to comments


Problem:

The requirement is to get top 10 employee’s name randomly from each department in a company. Let me elaborate it. Suppose in a company ABC, there are 1500 employees with 5 departments and each department have more than 200 employees. The manager wants to list down at least 10 names of the employees randomly from each department for a survey.

Solution:

In a given [Employees] table with columns [EmployeeName], [DeptName] we have some data, let’s say around 1500 records.

Use the below script to create and populate data in the [Employee] table:

CREATE TABLE [dbo].[Employees](
	[EmployeeName] [VARCHAR](50) NULL,
	[DeptName] [VARCHAR](50) NULL
) ON [PRIMARY]

To populate [Employees] table with dummy data:

DECLARE  @emptemp  TABLE
(
	[EmployeeName]  VARCHAR(50)
	,[DeptName] VARCHAR(50)
)
 
DECLARE @dept INT
DECLARE @employee INT
SET @dept = 65
SET @employee = 70
 
WHILE @dept < 70
BEGIN
	WHILE @employee < 90
	BEGIN
		INSERT INTO @emptemp
				   ([EmployeeName]
				   ,[DeptName])
			 VALUES( CHAR(@employee), CHAR(@dept) + CHAR(@dept + 1) )
 
		SET @employee = @employee + 1
	END 
 
	SET @employee = 70
	SET @dept = @dept + 1
END   
 
INSERT INTO [dbo].[Employees]
           ([EmployeeName]
           ,[DeptName])
   SELECT [EmployeeName]
           ,[DeptName] FROM @emptemp
 
SELECT * FROM dbo.Employees

Below are the different ways to get the problem solved:

  • By Using Cursor
/*
The below script will show random 10 records from each department from Employees table.
*/
 
DECLARE @deptColumn VARCHAR(50)
--For creation of a temporary table with no data.
SELECT * INTO #temporaryT
FROM dbo.Employees WHERE 1=2
 
DECLARE UserCursor CURSOR FOR
SELECT DISTINCT DeptName FROM dbo.Employees
 
OPEN UserCursor
FETCH NEXT FROM UserCursor
INTO @deptColumn
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
	INSERT INTO #temporaryT
	SELECT TOP 10 * FROM dbo.Employees WHERE DeptName = @deptColumn
		ORDER BY NEWID()  --newid() for random records
 
	FETCH NEXT FROM UserCursor
	INTO @deptColumn
END
 
CLOSE UserCursor
DEALLOCATE UserCursor
 
SELECT * FROM #temporaryT ORDER BY DeptName
DROP TABLE #temporaryT
  • By Using ROW_NUMBER: Here is the short and effective query to do the above operation.
SELECT DeptName,EmployeeName  FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY DeptName ORDER BY newid() ) AS RowID
, * FROM dbo.Employees ) InnerQ
WHERE InnerQ.RowID <= 10
--RANK() can also be used instead of ROW_NUMBER()
  • By using WITH : another descent code to get the above result set
WITH distinctDepts AS
(SELECT DISTINCT DeptName FROM dbo.Employees)
 
SELECT InnerQ.* FROM distinctDepts  d
CROSS APPLY
(SELECT TOP 10 * FROM dbo.Employees
	WHERE DeptName = d.DeptName
ORDER BY newid()) InnerQ

Hope these queries will be helpful in pulling down your stress level.

Categories: Tips N Tricks
  1. No comments yet.
  1. No trackbacks yet.