CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Bhavana
Bhavana

Reputation: 349

oracle 10g, select clause

suppose a table employees with 14 rows and i want to select only middle rows like from 3 to 10 then how it can be done?

Upvotes: 1

Views: 585

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

If your requirement is to get "all rows except for the top 3 and the bottom 3", regardless of the number of rows in the table, you could use this:

select * from (
  select emp.*,
         row_number() over (order by id) n,
         count(*) over () c
  from emp)
where n between 4 and c - 4;

If your requirement is to get "all the rows except for the top 20% and the bottom 20%", you could use this:

select * from (
  select emp.*,
         ntile(5) over (order by id) n,
         count(*) over () c
  from emp)
where n between 2 and 4;

Upvotes: 0

erbsock
erbsock

Reputation: 1217

If you are looking for a random sampling of rows, you can use the sample clause. It is based on percentages, however.

select * from table sample (5); -- 5 percent

Upvotes: 0

Rob van Wijk
Rob van Wijk

Reputation: 17705

First you'll have to define an ordering. Without an ordering, "middle rows like from 3 to 10" is meaningless. When you know the ordering, you can use almost the same query as RC proposed:

select *
  from ( select e.*
              , rownum rn
           from emp e
          order by <your ordering columns here>
       )
 where rn between 3 and 10

Regards,
Rob.

Upvotes: 3

RC.
RC.

Reputation: 28207

SELECT * FROM (SELECT e.*, rownum r FROM emp e) WHERE r BETWEEN 3 AND 10;

Upvotes: 2

Related Questions