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
  • 86,097
  • 7
  • 42
  • 90
Vytsalo
  • 422
  • 2
  • 12

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
  • 440,273
  • 77
  • 685
  • 758
Lalit Kumar B
  • 43,345
  • 10
  • 82
  • 112
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,354
  • 4
  • 8
  • 30