Display the alternate rows from MySQL table

To display alternate records from MYSQL table, suppose we have the following records -

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empdata` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Anjali', This email address is being protected from spambots. You need JavaScript enabled to view it.', 878433948),
(2, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', 493905490),
(3, 'Abhi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 403022139),
(4, 'Joya', This email address is being protected from spambots. You need JavaScript enabled to view it.', 342345329),
(5, 'Ammy', This email address is being protected from spambots. You need JavaScript enabled to view it.', 239848342),
(6, 'Lussi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 490290331);

These are the methods that you can use to get alternate or ODD-EVEN records from a MySQL table -

Method1 : 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.

SELECT * FROM empdata GROUP BY id having mod(id,2)=1;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 878433948 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 403022139 |
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
+----+--------+--------------------+-----------+

The above statement returns only ODD rows. If you want to get even rows, write the statement as-

SELECT * FROM empdata GROUP BY id having mod(id,2)=0;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 493905490 |
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 490290331 |
+----+--------+--------------------+-----------+

Method 2

We can also use the modulus operator instead of mod() method, like- the given statement returns only even rows-

SELECT * FROM empdata
WHERE id IN(SELECT id FROM empdata WHERE id%2 = 0);

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 493905490 |
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 490290331 |
+----+--------+--------------------+-----------+

similarly, the following statement returns only odd rows-

SELECT * FROM empdata
WHERE id IN(SELECT id FROM empdata WHERE id%2 = 1);

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 878433948 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 403022139 |
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
+----+--------+--------------------+-----------+