Fetch the ride provider details whose rides were mostly used by the users

Question

i have to fetch the details of ride provider who have offered their ride most to users.

the user_id in user_detail table is same as ride_provider_id in RIDE table.

i tried this but getting error 'ORA-00920: invalid relational operator' at last line ;

select u.* 
from user_details u, 
(select ride_provider_id,count(ride_provider_id) as of_ride
from ride 
group by ride_provider_id) r2
where u.user_id= r2.ride_provider_id
having max(r2.of_ride);

Answer 1

In Oracle, you can phrase this using order by and the fetch first clause:

select u.* 
from user_details u join
     (select ride_provider_id, count(*) as of_ride
      from ride 
      group by ride_provider_id
     ) r
     on u.user_id = r.ride_provider_id
order by of_ride desc
fetch first 1 row only;

However, this seems rather inefficient. If you are using a subquery for the count, then the query can do the limit in the subquery:

select u.* 
from user_details u join
     (select ride_provider_id, count(*) as of_ride
      from ride 
      group by ride_provider_id
      order by count(*) desc
      fetch first 1 row only
     ) r
     on u.user_id = r.ride_provider_id