Sequel can be used to fetch random records from a given dataset. One can easily use the rand() function to generate random floating point values between 0 and 1. I recently wrote about how to generate test data using just SQL. With this function, a lot of useful things can be done. For example,

• Disordering a dataset (or randomly ordering a dataset)
• Generating a random number & a random number within a given range
• Splitting a dataset into two or more parts (for testing & training)
• Generating random strings (when used in conjunction with md5()

There are a lot of other interesting use cases where rand() fits in. For the scope of this writeup, we’ll use a planet & satellite dataset hosted by Devstronomy. Here’s the DDL for the two tables —

CREATE TABLE `l_planet` (
	  `id` int(11) NOT NULL DEFAULT '0',
	  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
	  `mass` decimal(16,6) NOT NULL,
	  `diameter` decimal(16,6) NOT NULL,
	  `density` decimal(16,6) NOT NULL,
	  `gravity` decimal(16,6) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

	CREATE TABLE `l_satellite` (
	  `id` int(11) NOT NULL DEFAULT '0',
	  `planet_id` int(11) NOT NULL,
	  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
	  `radius` decimal(16,6) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Picking a Random Satellite from a Random Planet

A simple example showing the use of rand() in a subquery — in this example, the purpose is to pick a random satellite from a random planet every time the query is run. This, however, doesn’t guarantee that two subsequent results of this query aren’t the same. The query obviously doesn’t have any memory of what the result on the last run was.

select * 
	  from devstronomy.l_satellite 
	 where planet_id = (select id 
	                      from devstronomy.l_planet 
	                     order by rand() 
	                    limit 1) 
	 order by rand() 
	 limit 1;

#random #sql #software-development #analytics #mysql