Select Random Rows

Transact-SQL
1

Hello,

How can I select one random row?

**

Select TOP 1 Id, BulkId, Amount, ProductCode from BulkPurchases where status = 0

**

Thank you.

USE [GameAPI]
GO
/****** Object:  Table [dbo].[BulkPurchases]    Script Date: 28.01.2023 17:54:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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, 1, 100, 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, 100, N'0187209', 1, CAST(N'2023-01-25T15:47:00.000' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (4, 1, 100, 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, 1, 100, 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, 200, 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, 500, N'0187209', 1, CAST(N'2023-01-25T17:13:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] OFF
GO
2

Select TOP 1 Id, BulkId, Amount, ProductCode from BulkPurchases where status = 0 ORDER BY NEWID()

NEWID() generates a new GUID value which are essentially random. Note that if your table has millions of rows this could take time to evaluate.

3

thank you @robert_volk , not millions just thousands :slight_smile:
Can you please check my other post please?

4

Can you save us some time and post the link for it, please?

1 Like
5

sorry, here is the post; @JeffModen

https://forums.sqlteam.com/t/how-would-you-compare-an-excel-with-data-in-the-database-table/22341/18

6

Seems like Robert answered all but your last question.

As a bit of a sidebar, I use what are generically referred to as the "ACE" drives for SQL Server, which allows me to read from spreadsheets directly. If you have to do such a thing a whole lot, you might want to look into them. I also have a presentation on their use. I haven't, however, tested the methods on anything past 2017 simply because we're still using 2016 at work. Hopefully, we'll be migrating to 2022 before the end of the second quarter. This also reminds me that I have to download the Developer's Edition of 2022 and see if the drivers and the methods still work because we use them a whole lot.

7

@JeffModen thank you for your reply. The problem is not about inserting records from excel tough. I need to compare 2 tables in the SQL server. @robert_volk provided a query but at this moment I need to add another table to the query in order to get some additional data.

8

Understood. Like I said in the post above, it was a bit of a sidebar.

Also note that the title of that other post says nothing about comparing two tables. In fact, the title explicitly mentions that you're comparing "an excel" with data in a table. It might not be how you intended it to mean but it's easy to take that way.

1 Like