title: Sort a data.table fast by Ascending/Descending order
body: <p>I have a data.table with about 3 million rows and 40 columns. I would like to sort this table by descending order within groups like the following sql mock code:</p>
  
  <pre><code>sort by ascending Year, ascending MemberID, descending Month 
  </code></pre>
  
  <p>Is there an equivalent way in data.table to do this? So far I have to break it down into 2 steps:</p>
  
  <pre><code>setkey(X, Year, MemberID)
  </code></pre>
  
  <p>This is very fast and takes only a few second.</p>
  
  <pre><code>X &lt;- X[,.SD[order(-Month)],by=list(Year, MemberID)]
  </code></pre>
  
  <p>This step takes so much longer (5 minutes).</p>
  
  <p>Update:
  Someone made a comment to do <code>X &lt;- X[sort(Year, MemberID, -Month)]</code> and later deleted. This approach seems to be much faster:</p>
  
  <pre><code>user  system elapsed 
  5.560  11.242  66.236 
  </code></pre>
  
  <p>My approach: setkey() then order(-Month)</p>
  
  <pre><code>   user  system elapsed 
  816.144   9.648 848.798 
  </code></pre>
  
  <p>My question is now: if I want to summarize by Year, MemberId and Month after sort(Year, MemberID, Month), does data.table recognize the sort order?</p>
  
  <p>Update 2: to response to Matthew Dowle:</p>
  
  <p>After setkey with Year, MemberID and Month, I still have multiple records per group. What I would like is to summarize for each of the groups. What I meant was: if I use X[order(Year, MemberID, Month)], does the summation utilizes binary search functionality of data.table:</p>
  
  <pre><code>monthly.X &lt;- X[, lapply(.SD[], sum), by = list(Year, MemberID, Month)]
  </code></pre>
  
  <p>Update 3: Matthew D proposed several approaches. Run time for the first approach is faster than order() approach:</p>
  
  <pre><code>   user  system elapsed 
    7.910   7.750  53.916 
  </code></pre>
  
  <p>Matthew: what surprised me was converting the sign of Month takes most of the time. Without it, setkey is blazing fast.</p>