Table: Employee
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Ram | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Shyam | 60000 | 1 |
| 5 | Ram | 90000 | 2 |
| 7 | Leela | 80000 | 1 |
| 8 | Geeta | 70000 | 1 |
+----+-------+--------+--------------+
Result: Even
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 2 | Henry | 80000 | 2 |
| 4 | Shyam | 60000 | 1 |
| 7 | Leela | 80000 | 1 |
+----+-------+--------+--------------+
Result: Odd
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Ram | 85000 | 1 |
| 3 | Sam | 60000 | 2 |
| 5 | Ram | 90000 | 2 |
| 8 | Geeta | 70000 | 1 |
+----+-------+--------+--------------+
Approach:
Mysql
// For even selection
select ID, Name, Salary, DepartmentId
from (select e.*, (@rn := @rn + 1) as seqnum
from Employee e cross join
(select @rn := 0) vars
order by id
) t
where mod(seqnum, 2) = 0;
// even using primary key
select ID, Name, Salary, DepartmentId
from Employee where mod(id,2)=0;
// For Odd selection
select ID, Name, Salary, DepartmentId
from (select e.*, (@rn := @rn + 1) as seqnum
from Employee e cross join
(select @rn := 0) vars
order by id
) t
where mod(seqnum, 2) != 0;
// even using primary key
select ID, Name, Salary, DepartmentId
from Employee where mod(id,2)!=0;
Oracle
// For even selection
select ID, Name, Salary, DepartmentId from
(
select e.*, ROWNUM as r from Employee e
)
where MOD( r, 2) = 0;
// even using primary key
select ID, Name, Salary, DepartmentId
from Employee where mod(id,2)=0;
// For Odd selection
select ID, Name, Salary, DepartmentId from
(
select e.*, ROWNUM as r from Employee e
)
where MOD( r, 2) != 0;
// even using primary key
select ID, Name, Salary, DepartmentId
from Employee where mod(id,2)!=0;
No comments:
Post a Comment