Getting records randomly one by one based on a certain criteria

Transact-SQL
1

Hi there,

I would like to data get one by one where the status of all BulkID = 1 with randomly. Here is what I tried but didn't work as I wanted.

Thank you.

Select TOP 1 BulkId, ProductCode from BulkPurchases where status = 1 group by BulkID,ProductCode ORDER BY NEWID()

CREATE TABLE [dbo].[BulkPurchases](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BulkID] [int] NOT NULL,
	[Amount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Status] [int] NULL,
	[PurchaseDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] ON 
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (1, 1, 20, N'0187209', 1, CAST(N'2023-01-04T20:41:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (2, 2, 20, N'0187209', 1, CAST(N'2023-01-25T15:43:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (3, 1, 20, N'0187209', 0, CAST(N'2023-01-25T15:47:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (4, 1, 20, N'0187209', 1, CAST(N'2023-01-25T15:50:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (5, 2, 20, N'0187209', 1, CAST(N'2023-01-25T16:46:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (6, 1, 20, N'0187209', 1, CAST(N'2023-01-25T17:01:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (7, 1, 20, N'0187209', 1, CAST(N'2023-01-25T17:13:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] OFF
GO
2

You do not show what the output should look like but maybe:

WITH OrderedRows
AS
(
	SELECT BulkId, ProductCode
		,ROW_NUMBER() OVER (PARTITION BY BulkId, ProductCode ORDER BY NEWID()) AS rn
	FROM dbo.BulkPurchases
	WHERE [status] = 1
)
SELECT BulkId, ProductCode
FROM OrderedRows
WHERE rn = 1;
3

I think I have described it poorly, if the status of all relevant BulkIDs is 1, I want to retrieve them randomly one by one.

4

As SQL is set based this does not really make sense. If you want to process one row at a time return all the rows to your application and process them one at a time from there. If you really have to do this in SQL use a cursor.

5

Ok, I will do it as you said but how can I modify your query in order to get only if status = 1 based on bulkID. It should not bring BulkID = 1 because not all statuses 1.

Id BulkID Amount ProductCode Status PurchaseDateTime
1 1 100 0187209 0 2023-01-04 20:41:00.000
2 2 20 0187209 1 2023-01-25 15:43:00.000
3 1 1000 0187209 1 2023-01-25 15:47:00.000
4 1 20 0187209 1 2023-01-25 15:50:00.000
5 2 1000 0187209 1 2023-01-25 16:46:00.000
6 1 20 0187209 1 2023-01-25 17:01:00.000
7 1 20 0187209 1 2023-01-25 17:13:00.000

result of your query

BulkId ProductCode
1 0187209
2 0187209
6

This query works but does it need any improvement in terms of performance?

SELECT BulkID, ProductCode
FROM (
  SELECT BulkID, ProductCode, SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) as StatusCount
  FROM BulkPurchases
  GROUP BY BulkID, ProductCode
) as t
WHERE StatusCount = (SELECT COUNT(*) FROM BulkPurchases WHERE BulkID = t.BulkID)
7

I think this should do the same thing:

SELECT BulkID, ProductCode
FROM BulkPurchases
GROUP BY BulkID, ProductCode
HAVING MIN(Status)=1

This assumes Status can only be 1 or 0.

8

Yours is more performant right? :slight_smile:

9

Ifors query modified with your status = 1 requirement

;WITH OrderedRows
AS
(
	SELECT 
	     ROW_NUMBER() OVER ( ORDER BY NEWID()) AS rn
   	  ,  BulkId
	  ,  ProductCode
	FROM
		( select * 	FROM dbo.BulkPurchases	WHERE [status] = 1 ) a
)
SELECT 
     BulkId
   , ProductCode
FROM 
    OrderedRows
WHERE 
    rn = 1;
10

Which one to use :slight_smile:

11

For both SQL Queries

Compare the excecution plans ( cost )
Prepare some test data and do testing
Compare the performance stats like .. Time , Logical Reads , Network Round Trips

And then decide which one to use