Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 1987-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |   		  90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 1987-06-18 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 1987-06-19 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 1987-06-20 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 1987-06-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 1987-06-22 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 1987-06-23 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 1987-06-24 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 1987-06-25 | FI_MGR     | 12000.00 |           0.00 |        101 |           100 |
.........
|         206 | William     | Gietz       | WGIETZ   | 515.123.8181       | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110 |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Code:

-- Setting user-defined variable @i to 0
SET @i = 0; 
-- Selecting the sequential number and employee_id of every second row in the employees table
SELECT i, employee_id 
-- Subquery to generate a sequential number for each row in the employees table, starting from 1
FROM (SELECT @i := @i + 1 AS i, employee_id FROM employees) a 
-- Filtering the result set to include only rows with even sequential numbers
WHERE MOD(a.i, 2) = 0;

Explanation:

This MySQL code first initializes a user-defined variable @i and sets it to 0.
Then it selects a sequential number (i) and the employee_id of every second row in the employees table.
To achieve this, it uses a subquery where @i is incremented by 1 for each row of the employees table, effectively generating a sequential number for each row starting from 1.
Finally, it filters the result set to only include rows where the sequential number is even (indicating every second row) using the MOD function with a divisor of 2.

The code have been executed in MySQL Version : 5.4.

Here is the output

---+--------------
i  |  employee_id
---+--------------
2  |  101
4  |  103
6  |  105
8  |  107
10 |  109
12 |  111
14 |  113
16 |  115
18 |  117
20 |  119
22 |  121
24 |  123
........

Go to:

PREV :Write a MySQL query to display the employee ID, first name, last name, salary of all employees whose salary is above average for their departments.
NEXT :Write a MySQL query to find the 5th maximum salary in the employees table.

Structure of 'hr' database :

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.