Random Rows In Respect To Their Priorities - Sql Query

Introduction

If you have the table rows, priority wise, and you want to generate the rows randomly with a condition that the rows with highest priority shows more times as compared to the rows with lowest priority. You will learn this task after reading this article.

Scenario

For instance, we want to make a Banner and need to select it randomly from a huge list in respect to their priorities and date.

Banner Table

GO

CREATE TABLE [dbo].[Banner]
(
[Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkBanner_Id PRIMARY KEY,
[Title] [varchar](52) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Priority] [smallint] NOT NULL,
)

Sample Entries

GO

INSERT INTO
Banner
(Title, StartDate, EndDate, Priority)
VALUES
('Sample Banner 1','6/12/2007','11/12/2007',5)

GO

INSERT INTO
Banner
(Title, StartDate, EndDate, Priority)
VALUES
('Sample Banner 2','6/12/2007','6/21/2007',4)

GO

INSERT INTO
Banner
(Title, StartDate, EndDate, Priority)
VALUES
('Sample Banner 3','4/12/2007','11/12/2008',3)

GO

INSERT INTO
Banner
(Title, StartDate, EndDate, Priority)
VALUES
('Sample Banner 4','6/11/2007','11/12/2007',2)

GO

INSERT INTO
Banner
(Title, StartDate, EndDate, Priority)
VALUES
('Sample Banner 5','6/11/2007','11/12/2007',1)


Assuming that priority 5 is highest and 1 is lowest.


Please feel free to change the queries in respect to their date and priority.

Solution

SELECT TOP 1
Id,
Title,
StartDate,
EndDate,
Priority
FROM
Banner
WHERE
GetDate() BETWEEN StartDate AND EndDate
ORDER BY
RAND(CAST(CAST(NEWID() AS BINARY(4)) AS int))*Priority DESC

The above query generates the rows randomly every time when it executes in respect to their priorities and date.


It means that the banner with the highest priority shows more than the lowest priority. In addition, the current date should between StartDate And EndDate.

Summary

After reading this article you learn a query which shows the rows randomly in respect to their priorities.