rand() MySQL function to select random records from table

Here we have to use rand() function and along with it we will use limit query to restrict our result to one record. Random function is used along with order by clause to generate random records. This will work well in other database also. Here is the query. We have added on where clause also.

SELECT * FROM student4 ORDER BY RAND() LIMIT 1

We have one status column where data can be True or False. Here we are collecting random records by filtering based on status column value.

SELECT * FROM student4 WHERE status=True ORDER BY RAND() limit 1

To get 10 random records from the same table based on status column value.

SELECT * FROM student4 WHERE status=1 ORDER BY RAND() LIMIT 10

Random records from two class ( Four and Seven ) only.

SELECT * FROM student4 WHERE class IN ('Four', 'Seven') ORDER BY RAND() limit 5;

SELECT * FROM student4 WHERE class =IF(RAND() < 0.5, 'Four', 'Seven') ORDER BY RAND() limit 5;

UPDATING random records

Update 2 records randomly.

UPDATE student4  SET status=False ORDER BY RAND() limit 2

Only update if the status is not updated before (or status is True. )

UPDATE student4  SET status=False WHERE status=True ORDER BY RAND() limit 15

To update all records to make status as True.

UPDATE student4  SET status=True

CREATE Table using random records

Here we are creating table by using random 10 records.

CREATE  TABLE my_student  (
    SELECT * FROM student4
    ORDER BY RAND()
    LIMIT 10 
);

Using IF NOT EXISTS to avoid error.

CREATE  TABLE IF NOT EXISTS my_student  (
    SELECT * FROM student4
    ORDER BY RAND()
    LIMIT 10 
);

CREATE TABLE `student4` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `class` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `mark` int NOT NULL DEFAULT '0',
  `gender` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'male',
  `status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `student4`
--

INSERT INTO `student4` (`id`, `name`, `class`, `mark`, `gender`, `status`) VALUES
(1, 'John Deo', 'Four', 75, 'female', 1),
(2, 'Max Ruin', 'Three', 85, 'male', 1),
(3, 'Arnold', 'Three', 55, 'male', 1),
(4, 'Krish Star', 'Four', 60, 'female', 1),
(5, 'John Mike', 'Four', 60, 'female', 1),
(6, 'Alex John', 'Four', 55, 'male', 1),
(7, 'My John Rob', 'Five', 78, 'male', 1),
(8, 'Asruid', 'Five', 85, 'male', 1),
(9, 'Tes Qry', 'Six', 78, 'male', 1),
(10, 'Big John', 'Four', 55, 'female', 1),
(11, 'Ronald', 'Six', 89, 'female', 1),
(12, 'Recky', 'Six', 94, 'female', 1),
(13, 'Kty', 'Seven', 88, 'female', 1),
(14, 'Bigy', 'Seven', 88, 'female', 1),
(15, 'Tade Row', 'Four', 88, 'male', 1),
(16, 'Gimmy', 'Four', 88, 'male', 1),
(17, 'Tumyu', 'Six', 54, 'male', 1),
(18, 'Honny', 'Five', 75, 'male', 1),
(19, 'Tinny', 'Nine', 18, 'male', 1),
(20, 'Jackly', 'Nine', 65, 'female', 1),
(21, 'Babby John', 'Four', 69, 'female', 1),
(22, 'Reggid', 'Seven', 55, 'female', 1),
(23, 'Herod', 'Eight', 79, 'male', 1),
(24, 'Tiddy Now', 'Seven', 78, 'male', 1),
(25, 'Giff Tow', 'Seven', 88, 'male', 1),
(26, 'Crelea', 'Seven', 79, 'male', 1),
(27, 'Big Nose', 'Three', 81, 'female', 1),
(28, 'Rojj Base', 'Seven', 86, 'female', 1),
(29, 'Tess Played', 'Seven', 55, 'male', 1),
(30, 'Reppy Red', 'Six', 79, 'female', 1),
(31, 'Marry Toeey', 'Four', 88, 'male', 1),
(32, 'Binn Rott', 'Seven', 90, 'female', 1),
(33, 'Kenn Rein', 'Six', 96, 'female', 1),
(34, 'Gain Toe', 'Seven', 69, 'male', 1),
(35, 'Rows Noump', 'Six', 88, 'female', 1);
COMMIT;

SQL References Part I Learn the basic of sql group by command

Subscribe to our YouTube Channel here

Dev Pandey	12-04-2012
select * from tableName order by rand() limit 0,1;

SQL for collecting random record from a table

UPDATING random records

CREATE Table using random records

Subscribe