Q:

Display the alternate rows from MySQL table

belongs to collection: MySQL Exercises

YasminRadwan26
Save to bookmark
|
2022-05-14
|
  188 
0

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', 'anjali@example.com', 878433948),
(2, 'Priska', 'priska@example.com', 493905490),
(3, 'Abhi', 'abhi@example.com', 403022139),
(4, 'Joya', 'joya@example.com', 342345329),
(5, 'Ammy', 'ammy@example.com', 239848342),
(6, 'Lussi', 'lussi@example.com', 490290331);

 

Sql programming,
login and add your own answer to this question

All Answers

YasminRadwan26
2022-05-14
need an explanation for this answer? contact us directly to get an explanation for this answer

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 | anjali@example.com | 878433948 |
|  3 | Abhi   | abhi@example.com   | 403022139 |
|  5 | Ammy   | ammy@example.com   | 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 | priska@example.com | 493905490 |
|  4 | Joya   | joya@example.com   | 342345329 |
|  6 | Lussi  | lussi@example.com  | 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 | priska@example.com | 493905490 |
|  4 | Joya   | joya@example.com   | 342345329 |
|  6 | Lussi  | lussi@example.com  | 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 | anjali@example.com | 878433948 |
|  3 | Abhi   | abhi@example.com   | 403022139 |
|  5 | Ammy   | ammy@example.com   | 239848342 |
+----+--------+--------------------+-----------+

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

MySQL Exercises
This question belongs to these collections

Similar questions

need a help?

find thousands of online teachers now
MySQL update multiple rows in one query... >>
<< Delete duplicate row from MySQL table...