I understand that this is SQL Oracle question.
But I have not touched Oracle in more than a decade, so here is a way of doing it in tsql :)
Hopefully you should be able to convert to be useful in Oracle.
with employee as (
select
1 empno,
'A' ename,
7000 sal
union all select 2, 'B', 30000
union all select 3, 'C', 2000
union all select 4, 'D', 10000
union all select 5, 'E', 500
)
select *
from
(select
row_number() over (order by sal desc) rowid,
0 Sort,
count(empno) over () cnt,
*
from employee
union all
select
row_number() over (order by sal asc) rowid,
1 Sort,
count(empno) over () cnt,
*
from employee) employeeHiLo
where rowid <= (floor(cnt/2) + cnt%2)
order by rowid, sort
;
I believe there is an row_number() equivalent in Oracle, so the conversion should be possible.
The cnt column is a bit of a dirty way to figure out how many records to return.
When the sal values are the same, then you may have to add an additional ordering.
Also when an odd number of records are present the high and the low will be the same record, you get an additional row/record. If this is not what you want then fiddle with where clause for odd number of records.
Hope that helps out.
--------------------------------------------------
If you are interested, here is my first attempt.
Gets the hi and lo records in one record.
with employee as (
select
1 empno,
'A' ename,
7000 sal
union all select 2, 'B', 30000
union all select 3, 'C', 2000
union all select 4, 'D', 10000
union all select 5, 'E', 500
), employeeHiLo as (
select
row_number() over (order by sal desc) hilo,
row_number() over (order by sal asc) lohi,
count(empno) over () cnt,
*
from employee
)
select
*
from employeeHiLo employeeHi
inner join employeeHiLo employeeLo
on employeeHi.hilo = employeeLo.lohi
where employeeHi.hilo <= (floor(employeeHi.cnt/2) + employeeHi.cnt%2)
;