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.

kindly post the underlying table structure and desired output for further analysis.

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.

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)

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.