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
  • 143,790
  • 11
  • 59
  • 117
Vytsalo
  • 670
  • 3
  • 9
  • 19

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
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
  • 35,427
  • 4
  • 10
  • 31