title: Top N rows by group in ClickHouse
body: <p>What is the proper way to query top N rows by group in ClickHouse?<br>
  Lets take an example of tbl having id2, id4, v3 columns and N=2.
  I tried the following</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT                                                                          
      id2,                                                                        
      id4,                 
      v3 AS v3        
  FROM tbl
  GROUP BY                 
      id2,                 
      id4                  
  ORDER BY v3 DESC                                                                
  LIMIT 2 BY                       
      id2,                 
      id4      
  </code></pre>
  
  <p>but getting error</p>
  
  <pre><code>Received exception from server (version 19.3.4):
  Code: 215. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception
  : Column v3 is not under aggregate function and not in GROUP BY..
  </code></pre>
  
  <p>I could put <code>v3</code> into GROUP BY and it does seems to work, but it is not efficient to group by a metric.</p>
  
  <p>There is <code>any</code> aggregate function, but we actually want <code>all</code> values (limited to 2 by LIMIT BY clause) not <code>any</code> value, so it doesn't sound like to be proper solution here.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT                                                                          
      id2,                                                                        
      id4,                 
      any(v3) AS v3        
  FROM tbl
  GROUP BY                 
      id2,                 
      id4                  
  ORDER BY v3 DESC                                                                
  LIMIT 2 BY                       
      id2,                 
      id4      
  </code></pre>