0

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 ?

0
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 */

?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.