title: MySQL top-N ranking for each group, based on latest group's rows
body: <p>For each group of <code>TS</code>, there's random number of rows that having different <code>Name</code> and it's <code>Count</code>. I would like to do the following:</p>
  
  <ol>
  <li>Get latest group of TIMESTAMP with NAME order desc by <code>Count</code>. For more than 3 rows in the latest group, the rest sum by <code>Count</code> and rename as "Other".</li>
  <li>For each of the <code>TIMESTAMP</code> group, find rows having <code>NAME</code> in step 1.</li>
  <li>If not found, 0 padding as <code>Count</code> for the pair in specific group.</li>
  </ol>
  
  <p>Have a look at the illustration:</p>
  
  <pre><code>TS         | Name     | Count
  =============================
  1552286160 | Apple    | 7
  1552286160 | Orange   | 8
  1552286160 | Grape    | 8
  1552286160 | Pear     | 9
  1552286160 | Kiwi     | 10
  1552286160 | Berry    | 3
  1552286160 | Carrot   | 2
  
  1552286100 | Apple    | 10
  1552286100 | Orange   | 12
  1552286100 | Grape    | 14
  1552286100 | Pear     | 16
  1552286100 | Kiwi     | 9
  
  1552286040 | Apple    | 4
  
  1552285980 | Peach    | 8
  </code></pre>
  
  <p>With this dataset, I would like to have the following:</p>
  
  <pre><code>TS         | Name     | Count
  =============================
  1552286160 | Kiwi     | 10
  1552286160 | Pear     | 9
  1552286160 | Grape    | 8
  1552286160 | Other    | 8 + 7 + 3 + 2
  
  1552286100 | Kiwi     | 9
  1552286100 | Pear     | 16
  1552286100 | Grape    | 14
  1552286100 | Other    | 10 + 9
  
  1552286040 | Kiwi     | 0
  1552286040 | Pear     | 0
  1552286040 | Grape    | 0
  1552286040 | Other    | 4
  
  1552286040 | Kiwi     | 0
  1552286040 | Pear     | 0
  1552286040 | Grape    | 0
  1552286040 | Other    | 8
  </code></pre>
  
  <p>This is actually an extended solution came from <a href="https://stackoverflow.com/questions/55098157/mysql-top-n-ranking-and-sum-the-rest-of-same-group">MySQL top-N ranking and sum the rest of same group</a> However, the solution does not group each <code>TS</code> group with the same rows from the latest <code>TS</code> group, in this case <code>TS=1552286160</code>.</p>
  
  <p>The SQL Fiddle that I've prepared is located here: <a href="http://sqlfiddle.com/#!9/4e13004/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/4e13004/1</a></p>
  
  <p>Thanks if you have any idea.</p>