1

I would like to get records from 25 to 50. I write this code, but, it looks terrible with double select clause. 

Select * From (
    Select eto.*, rownum rn from employee_trip_orders eto
) where rn between 25 and 50 ;

How can i shrink it to use one select like that? 

 Select eto.*, eto.rownum rn from employee_trip_orders eto
 where rn between 25 and 50 ;

I don't need the second one. Thanks. I have old 11c Oracle version and offset keyword is not suitting me

MT0
  • 113,669
  • 10
  • 50
  • 103
Vytsalo
  • 502
  • 1
  • 6
  • 14

2 Answers2

1

How can i shrink it to use one select like that?

Since you are on Oracle 11g you cannot. You must use subquery inline to achieve your desired output.

Select eto.*, eto.rownum rn from employee_trip_orders eto
where rn between 25 and 50 ;

That query will never return a row. ROWNUM value is incremented only after it is assigned. Please see How ROWNUM works in pagination query.

From Oracle 12c onwards, you could use the new Top-n Row limiting feature.

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Lalit Kumar B
  • 45,678
  • 12
  • 90
  • 118
0

You are not using order by clause so what is the meaning of the rownum? In the end, You are only fetching random 26 (25-50 inclusive) records.

So You can achieve the desired result using the following code:

Select eto.*, rownum rn 
  from employee_trip_orders eto
 where rownum<= 26 ;

Cheers!!

Popeye
  • 34,995
  • 4
  • 9
  • 31