title: Oracle ListaGG, Top 3 most frequent values, given in one column, grouped by ID
body: <p>I have a problem regarding SQL query , it can be done in "plain" SQL, but as I am sure that I need to use some group concatenation (can't use MySQL) so second option is ORACLE dialect as there will be Oracle database. Let's say we have following entities:</p>
  
  <p>Table: Veterinarian visits</p>
  
  <pre><code>Visit_Id, 
  Animal_id, 
  Veterinarian_id, 
  Sickness_code
  </code></pre>
  
  <p>Let's say there is 100 visits (100 visit_id) and each animal_id visits around 20 times.</p>
  
  <p>I need to create a <code>SELECT</code> , grouped by Animal_id with 3 columns </p>
  
  <ul>
  <li>animal_id</li>
  <li>second shows aggregated amount of flu visits for this particular  animal (let's say flu, sickness_code = 5)</li>
  <li>3rd column shows top three sicknesses codes for each animal (top 3 most often codes for this particular animal_id)</li>
  </ul>
  
  <p>How to do it? First and second columns are easy, but third? I know that I need to use LISTAGG from Oracle, OVER PARTITION BY, COUNT and RANK, I  tried to tie it together but didn't work out as I expected :(  How should this query look like?   </p>