0

select eid from(select count(*) cnt,eid from certified group by eid)as foo where cnt=(select max(cnt) from (select count(*) cnt,eid from certified group by eid)as goo); This query displays the eid with maximum no of entries in the table certified. Which in this case is no. of aircraft licensed to an eid.
This query feels very processor heavy to me. How can i optimize it.

Edited by gauravk_bhanot: n/a

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by gauravk_bhanot
0
EID  AID
---  ---
21   101
22   102
22   103
21   104
24   102
25   102
21   105

This is the table which contains the EID of pilots and AID refers to the id of aricraft they are licensed to fly.I need to find the pilot EID who is licensed to fly maximum no. aircraft.
The query i gave in the OP displays the correct result.But i want to optimize for better performance.

0

try this

select eid from certified
where eid = (select eid from 
(select eid,count(aid) from certified
group by eid
order by count(aid) desc)
                        where rownum<2)
0

try this

select eid from certified
where eid = (select eid from 
(select eid,count(aid) from certified
group by eid
order by count(aid) desc)
                        where rownum<2)

Thank you.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.