Help with optimizing Oracle Query - nested with count

Question

I have 2 tables (Students and Cellphone).

Columns in Students: StudentID, FNAME, PHONEID

Columns in Cellphone: PhoneID, Phonename

I want to create this:

• Run a query to show the most popular phone used by students

My answer is:

select D.phoneid, c.phonename, d.numberp
from
(select * from 

(SELECT m.phoneid, 
         COUNT(*) AS numberp
    FROM students m
GROUP BY m.phoneid
ORDER BY numberp DESC)

where rownum = 1) D, cellphone c
where d.phoneid = c.phoneid

Can this be optimized or a simpler way of achieving this? May be using TOP ? or HAVING ? JOINS ?

Answer 1

SELECT c.phoneid, COUNT( /* DISTINCT*/ s.StudentID)
FROM Students s, Cellphone c
WHERE s.phoneid = c.phoneid
GROUP BY c.phoneid
ORDER BY 2 DESC
LIMIT FIRST 1 ROW /* WITH TIES */

?