title: Sort by one variable, group by another, and select first row in SQL Query in R
body: <p>I need to apply a procedure in SQL that is easy for me since R, but has been really tortuous in SQL.</p>
  <p>I need to sort the data from highest to lowest by two variables, group based on another variable, and select the first item in each group.</p>
  <p>I leave the code that I am trying to pass from R to SQL. Unfortunately the dbplyr package throws me an error when trying to convert one language to another: Error: <code>first()</code> is only available in a windowed (<code>mutate()</code>) context</p>
  <pre><code>library(tidyverse)
  library(dbplyr)
  con &lt;- DBI::dbConnect(RSQLite::SQLite(), &quot;:memory:&quot;)
  copy_to(con, mtcars)
  
  mtcars2 &lt;- tbl(con, &quot;mtcars&quot;)
  mtcars2
  
  mtcars2 %&gt;% 
    arrange(-mpg,-disp) %&gt;% 
    group_by(cyl) %&gt;% 
    summarise(hp = first(hp)) %&gt;% 
    show_query()
  </code></pre>
  <p>It seems to me that the DISTINCT ON function could help me.</p>
  <p>Thanks for your help.</p>