title: Finding max, second max, and third max aggregated by two parameters in R or with R's sqldf
body: <p>So I am trying to find the max, second max, and third max water use per customer ID per year for a dataset. I'm using R and the sqldf library, but am open to any R solutions to this. Here's a bit of sample data:</p>
  
  <pre><code> Year  | ID | Month  | Use |
  ----------------------------
  2009    101 1   103
  
  2009    101 2   209
  
  2009    101 3   375
  
  2009    101 4   360
  
  2010    101 1   170
  
  2010    101 2   381
  
  2010    101 3   275
  
  2010    101 4   260
  
  2009    102 1   263
  
  2009    102 2   234
  
  2009    102 3   45
  
  2009    102 4   275
  
  2010    102 1   469
  
  2010    102 2   107
  
  2010    102 3   354
  
  2010    102 4   436
  </code></pre>
  
  <p>Ideally I would want to return three matrices, <code>max1</code>, <code>max2</code>, <code>max3</code> with columns <code>ID</code>, <code>Year</code>, <code>Max</code> (or second max or third max, respectively)
  So  <code>max1 = [101, 2009, 375, 101, 2010, 381, 102, 2009, 275, 102, 2010, 469]</code> etc.</p>
  
  <p>My initial approach was to make a nested for loop with <code>listofIDs</code> and <code>listofYears</code> as the domains of <code>ID</code> and <code>Year</code>, like:</p>
  
  <pre><code>for i in 1:length(listofIDs){
  
  for y in 1:length(listofYears){
  
  monthlylist&lt;-sqldf("select Month, Use from Dataframe where ID=listofIDs[i] and Year=listofYears[y]")
  </code></pre>
  
  <p>and then sort <code>monthlylist</code> and pull out the max's, etc.
  But <code>sqldf</code> won't read variables like that so I would have to explicitly state where <code>ID = 101</code>, where <code>ID = 102</code> each time.</p>
  
  <p>Any ideas on how to get sqldf to recognize my varibles, or a better way to find the max, second max, and third max aggregated by year and ID? I am working with big datasets so ideally something that doesn't take forever.</p>