EF Core - Getting records randomly based on a certain criteria

Question

Hi there,

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

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

Sample Data:

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

expected result

BulkID	ProductCode
2	0187209

Thank you.

Answer 1

This works but do any suggestions for improvement?

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)

Answer 2

Here is what I tried but didn't work as I wanted. ORDER BY NEWID()

The function NEWID() in your query is evaluated once and so it returns one value for all; yes, that don't work

I would like to get data one by one ... randomly

Coincidence isn't something that fits into the database world; What's that good for?

Answer 3

change your demand please.