SQL for collecting random record from a 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


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Dev Pandey

    12-04-2012

    select * from tableName order by rand() limit 0,1;
    alex

    11-04-2013

    What is the use of Random records ?

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer