Fetch Unique random Record in sql server

Fetch Unique random Record in sql server

In This Artical You can easily fetch unique and random record And you get each time unique record if you execute this query every time you fetch unique and random record using Newid() function.
Follow some steps-

• Step 1 - Firstly Create Data base and create Table See in Example.
• Step 2 - Create table after that insert some record and select records.
• Step 3 - Now get some random and unique record than use new id function() ,this function fetch                       random record each time

Example

Step 1-
CREATE TABLE Tbl_Customer
(
      ID INT PRIMARY KEY IDENTITY(1,1),
      Name NVARCHAR(200),
       Salary DECIMAL(18,2)
)
Step 2-
Insert Record -
INSERT INTO Tbl_Customer(Name,Salary)VALUES('RAJ',25000)
Insert multiple records and select with select query
Select * from Tbl_Customer
Step 3-
SELECT TOP 1 * FROM Tbl_Customer ORDER BY NEWID()
Fetch unique and random record then use this query-
if you execute this query then got one record and next time execute then got different record so you can easily find unique and random records using NewId() function.

The key here is the NEWID function, which generates a globally unique identifier (GUID) in memory for each row.By definition, the GUID is unique and fairly random; so, when you sort by that GUID with the ORDER BY clause, you get a random ordering of the rows in the table. So finaly we get Unique and Random rows by using this query. We use Top 1 its use to only Top 1 result in table.

SQL Select Top

In this artical filtring records according SELECT TOP clause. The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. You can select top 10 percent also and filter record according percents.

Example

Create table tbl_Employee in SQL Server.
CREATE TABLE Tbl_Employee
(
Id int primary key identity(1,1),
Name nvarchar(50),
Salary decimal(18,2)
)

Insert Records in table with Insert Query like.Write insert query and execute (f5).

INSERT INTO Tbl_Employee VALUES('JEET',30000)

Inserted record and after that Select table and show data.

Example

SELECT * FROM Tbl_Employee

Output

Now Select table and table return all rows you can Apply select Top then Write query select top like

Example Select Top

Syntex

SELECT TOP number|percent ColumnName(s) FROM TableName
SELECT Top 5 * FROM Tbl_Employee

Output

Note- In this example Select Top 5 then show only 5 records and you can Write top6 ,7,8...etc and get result according top numbers. Select Top number you want to give percent then write percent also like. In this example select top 50 percent then record give just half of all records.It is filter According percentage.

Example Select Top

Syntex

SELECT Top 50 PERCENT * FROM Tbl_Employee

Rank Given In Table

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of  ROW_NUMBER() Function 
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id  CityName   C_Id
1    Allahabad    1
2    Allahabad   1
3    Allahabad    1
4    Patna        1
5    Patna        2
6    Patna        3
7    Varanasi    3
8    Varanasi    3
9    Varanasi    3
10    Varanasi    3

Give Rank 

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName  C_Id RowNo SameRank
1    Allahabad     1    1     1
2    Allahabad     1    2     1
3    Allahabad     1    3     1
4    Patna             1    2     2
5    Patna             2    2     2
6    Patna             3    2     2
7    Varanasi        3    1     3
8    Varanasi        3    2     3
9    Varanasi        3    3     3
10    Varanasi      3    4     3