MySql MOD function

MySql MOD function

MOD function returns the remainder of a number divided by another number.

MySql MOD Syntax,

SELECT MOD(number1,number2);

or

SELECT number1 MOD number2;

or

SELECT number1 % number2;

Let us see the example to compute MOD value for each row in mysql table 'tblemployee'.

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select MOD(emp_id, 2) from tblemployee1;
+----------------+
| MOD(emp_id, 2) |
+----------------+
|              1 |
|              0 |
|              1 |
|              0 |
+----------------+
4 rows in set (0.01 sec)

mysql> select (emp_id MOD 2)  from tblemployee1;
+----------------+
| (emp_id MOD 2) |
+----------------+
|              1 |
|              0 |
|              1 |
|              0 |
+----------------+
4 rows in set (0.00 sec)

mysql> select (emp_id % 2)  from tblemployee1;
+--------------+
| (emp_id % 2) |
+--------------+
|            1 |
|            0 |
|            1 |
|            0 |
+--------------+
4 rows in set (0.00 sec)

mysql>

Retrieve alternate rows in mysql table

we can also use MOD function to retrieve the alternate records from a mysql table.

Let us consider the mysql tblemployee table and explains mysql query to find alternate even emp_id rows in a table 'tblemployee'.

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> select * from tblemployee where mod(emp_id,2)=0;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      2 | emp2     |      11 |  20000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
2 rows in set (0.01 sec)

mysql query to find alternate odd emp_id rows in a table 'tblemployee'.

mysql> select * from tblemployee where mod(emp_id,2)=1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      3 | emp3     |      10 |  15000 |
+--------+----------+---------+--------+
2 rows in set (0.00 sec)

« Previous Next »