Here we show how you can display only even or odd values rows in MySql. Sometime we need to display alternative records from MySql table.
Below is a sample table with data
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` (`id`, `name`, `subject`, `address`, `gender`, `class`, `age`) VALUES
(1, 'Sanjay', 'Math', 'Noida', 'Male', '12th', 18),
(2, 'Devendra', 'Physics', 'Noida', 'Male', '10th', 17),
(3, 'Rajendra', 'Chemistry', 'Lucknow', 'Male', '12th', 21),
(4, 'Rani', 'Social Science', 'Delhi', 'Female', '9th', 13),
(5, 'Saroj', 'English', 'Rampur', 'Female', '12th', 17),
(6, 'Arvind', 'Hindi', 'Lucknow', 'Male', '10th', 16),
(7, 'Mahesh', 'Math', 'Moradabad', 'Male', '12th', 20),
(8, 'Kuldeep', 'Physics', 'Lucknow', 'Male', '12th', 19),
(9, 'Amit', 'English', 'Kanpur', 'Male', '12th', 18),
(10, 'Navdeep', 'Biology', 'Lucknow', 'Male', '10th', 15),
(11, 'Vedant', 'Biology', 'Delhi', 'Male', '9th', 14),
(12, 'Mukul', 'English', 'Delhi', 'Male', '12th', 17);
--
-- Indexes for table `student`
--
ALTER TABLE `student`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;
These are the methods that you can use to get alternate or ODD-EVEN records from a MySQL table.
1)- MySQL MOD() method-
MySQL MOD() method returns the remainder of a number divided by another number. So for getting alternate rows, we can divide the ID with 2 and displays only those having remainder 1 for odd records and remainder 0 for even records.
SELECT * FROM `student` WHERE mod(id,2)= 0;
Above statement returns even value records. Below show the output of above query.
SELECT * FROM `student` WHERE mod(id,2)= 1;
Above statement returns odd value records. Below show the output of above query.
2)- Modulo Operator-
We can also use the modulo operator(%) instead of mod() method.
SELECT * FROM `student` WHERE id%2 = 0
Above statement returns even value records. Below show the output of above query.
SELECT * FROM `student` WHERE id%2 = 1;
Above statement returns odd value records. Below show the output of above query.
I hope this article helps you.
Thanks for visiting Inflay.com .