A decision has to taken carefully before we implement a solution for the two possibilities -

1. When we have odd total number of rows
2. When we have even total number of rows

My SQL will work differently in each case.

In first case (odd number of total rows), this SQL query will return middle record with out any conflict. Eg., If we have 9 records, this will display 5th record from table.

In second case (even number of total rows), this SQL query will return middle two records as we can't find single middle record. Eg., if we have 14 records in table, it will display both 7th and 8th rows.

select * from
(select rownum sno, empno,ename from emp)
where
sno in
(
select floor((count(*)+1)/2) eg from emp
union
select ceil((count(*)+1)/2) eg from emp
);


output -
--------------------------------------------------
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from
2 (select rownum sno, empno,ename from emp)
3 where
4 sno in
5 (
6 select floor((count(*)+1)/2) eg from emp
7 union
8 select ceil((count(*)+1)/2) eg from emp
9 );

SNO EMPNO ENAME
---------- ---------- ----------
7 7782 CLARK
8 7788 SCOTT

SQL> insert into emp(empno) values (20);

1 row created.

SQL> select count(*) from emp;

COUNT(*)
----------
15

SQL> select * from
2 (select rownum sno, empno,ename from emp)
3 where
4 sno in
5 (
6 select floor((count(*)+1)/2) eg from emp
7 union
8 select ceil((count(*)+1)/2) eg from emp
9 );

SNO EMPNO ENAME
---------- ---------- ----------
8 7782 CLARK

SQL>