sqloracle-databasetop-n

How do I find the most frequent number from patient_id


An image of the table to retrieve the data from:

REM ADMISSION TABLE

INSERT INTO ADMISSION VALUES (205,101,'2/2/2011','HB',114,'P','21/2/2011');


Solution

  • What is "most frequent number"? Patient who has most admissions? If so, then

    select patient_id
    from (select patient_id,
                 rank() over (order by count(*) desc) rnk
          from admission
          group by patient_id
         )
    where rnk = 1;