How to get n random number of records from table in Sql Server



Description: While working with sql server database we may require selecting some records randomly every time we fetch records from the table. suppose we have employee table having 100 records and we want to select any 5 records randomly from the table in each call.  This could be done easily using sql server's inbuilt function NEWID() in order by clause in the select query.

The NEWID() function in SQL Server creates a unique value of type uniqueidentifier.  One use of the NEWID() function is in generating random rows from a table as we are going to use.

Implementation: Let's see by suitable example:

--Create employee table using below script.


CREATE TABLE  dbo.tbEmployeeMaster
(
    EmployeeId     INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    EmployeeName   NVARCHAR(50),
    Gender         NVARCHAR(10),
    Department     NVARCHAR(50)
) 

--Add some dummy data into this table
GO

INSERT INTO dbo.tbEmployeeMaster(EmployeeName,Gender,Department)
VALUES
('Arjun','Male','Administration'),
('Rohan','Male','Sales'),
('Ishita','Female','HRM'),
('Aadi','Male','Sales'),
('Preetam','Male','HRM'),
('Anjan','Male','Administration'),
('Rajesh','Male','HRM'),
('Ankur','Male','HRM'),
('Robin','Male','Finance'),
('Mayank','Male','Sales'),
('Manisha','Female','HRM'),
('Sonam','Female','HRM'),
('Rajan','Male','HRM'),
('Kapil','Male','Sales'),
('Ritika','Female','HRM'),
('Akshay','Male','Finance'),
('Aryan','Male','HRM'),
('Anju','Female','Finance'),
('Sapna','Female','Finance'),
('Ruhi','Female','Finance'),
('Robin','Male','Sales'),
('Neelam','Female','HRM'),
('Rajni','Female','Administration'),
('Sonakshi','Female','Finance');

 GO

Check data in table
SELECT *  FROM tbEmployeeMaster

Output will be as:
EmployeeId
EmployeeName
Gender
Department
1
Arjun
Male
Administration
2
Rohan
Male
Sales
3
Ishita
Female
HRM
4
Aadi
Male
Sales
5
Preetam
Male
HRM
6
Anjan
Male
Administration
7
Rajesh
Male
HRM
8
Ankur
Male
HRM
9
Robin
Male
Finance
10
Mayank
Male
Sales
11
Manisha
Female
HRM
12
Sonam
Female
HRM
13
Rajan
Male
HRM
14
Kapil
Male
Sales
15
Ritika
Female
HRM
16
Akshay
Male
Finance
17
Aryan
Male
HRM
18
Anju
Female
Finance
19
Sapna
Female
Finance
20
Ruhi
Female
Finance
21
Robin
Male
Sales
22
Neelam
Female
HRM
23
Rajni
Female
Administration
24
Sonakshi
Female
Finance

Now lets write the query to get 5 random records on every run.

First run:
SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:
EmployeeId
EmployeeName
Gender
Department
22
Neelam
Female
HRM
6
Anjan
Male
Administration
19
Sapna
Female
Finance
15
Ritika
Female
HRM
24
Sonakshi
Female
Finance

Second run:
SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:
EmployeeId
EmployeeName
Gender
Department
22
Neelam
Female
HRM
2
Rohan
Male
Sales
12
Sonam
Female
HRM
15
Ritika
Female
HRM
7
Rajesh
Male
HRM

Third run:
SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:
EmployeeId
EmployeeName
Gender
Department
7
Rajesh
Male
HRM
6
Anjan
Male
Administration
12
Sonam
Female
HRM
11
Manisha
Female
HRM
2
Rohan
Male
Sales


Conclusion: As we can see , we get random 5 records in each run. They can be same or different in each run.We can't predict which records will be fetched.

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Newest
Previous
Next Post »

1 comments:

Click here for comments
January 17, 2020 ×

Nice!!! it works.

Congrats bro LeMaNú Software you got PERTAMAX...! hehehehe...
Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..