0
select Customer_Name from Customer where Customer_Id IN 
(select distinct Customer.Customer_Id from Customer join Toy_Rental on Toy_Rental.Customer_ID=Customer.Customer_Id 
order by Toy_Rental.Total_Amount desc 
fetch next 2 rows only);
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
Shubham Agarwal
  • 1
  • 1
    See https://stackoverflow.com/questions/27099414/how-to-add-offset-in-a-select-query-in-oracle-11g for how to limit your result set in older versions of Oracle. – Dave Costa Jul 31 '20 at 19:09

1 Answers1

0

There's no FETCH clause in 11g; it is available in 12c.

See if this helps:

select a.Customer_Name 
from Customer a
where a.Customer_Id IN (select x.customer_id
                        from (select c.Customer_Id 
                              from Customer c join Toy_Rental t on t.customer_id = c.customer_id
                              order by t.total_amount desc
                             ) x
                        where rownum <= 2
                       )

Alternatively:

with cust as
  (select c.customer_id
          row_number() over (order by t.total_amount desc) rn
   from customer c join toy_rental t on t.customer_id = c.customer_id
  )
select a.customer_name
from customer a join cust x on x.customer_id = a.customer_id
where x.rn <= 2
Littlefoot
  • 131,892
  • 15
  • 35
  • 57