title: PostgreSQL Select highest value within time period
body: <p>Using Rails, I'm trying to perform a SQL command to return an array of rows that contain the highest value for a certain user per day.</p>
  
  <p>For example:</p>
  
  <pre><code>  user_id(integer) |        created_at(datetime)    | score(integer)
  -------------------+--------------------------------+---------------
                1    |      "2015-07-27 21:35:24"     |         100
                1    |      "2015-07-27 21:35:24"     |         123
                2    |      "2015-07-27 21:35:24"     |         101
                2    |      "2015-07-27 21:35:24"     |         122
                3    |      "2015-07-27 21:35:24"     |         103
                3    |      "2015-07-27 21:35:24"     |         115
                1    |      "2015-07-26 21:35:24"     |         116
                1    |      "2015-07-26 21:35:24"     |         151
                2    |      "2015-07-26 21:35:24"     |         122
                2    |      "2015-07-26 21:35:24"     |         134
                3    |      "2015-07-26 21:35:24"     |         123
                3    |      "2015-07-26 21:35:24"     |         111
                1    |      "2015-07-25 21:35:24"     |         129
                1    |      "2015-07-25 21:35:24"     |         152
                2    |      "2015-07-25 21:35:24"     |         120
                2    |      "2015-07-25 21:35:24"     |         109
                3    |      "2015-07-25 21:35:24"     |         142
                3    |      "2015-07-25 21:35:24"     |         131
  </code></pre>
  
  <p>Expected results:</p>
  
  <pre><code>  user_id(integer) |        created_at(datetime)    | score(integer)
  -------------------+--------------------------------+---------------
                1    |      "2015-07-27 21:35:24"     |         123
                2    |      "2015-07-27 21:35:24"     |         122
                3    |      "2015-07-27 21:35:24"     |         115
                1    |      "2015-07-26 21:35:24"     |         151
                2    |      "2015-07-26 21:35:24"     |         134
                3    |      "2015-07-26 21:35:24"     |         123
                1    |      "2015-07-25 21:35:24"     |         152
                2    |      "2015-07-25 21:35:24"     |         120
                3    |      "2015-07-25 21:35:24"     |         142
  </code></pre>
  
  <p>I've been combining various joins, <code>having</code>, and other methods, but to no avail. I can't get it to filter the results. I've made a little bit of progress by <code>select</code>ing max values per day, but then I can't filter out the lower ones on a per <code>user_id</code> basis. I managed to do it using multiple <code>group_by</code> and <code>map</code> in Rails, but it's super slow since it has to re-iterate over the entire array, and since there is a lot of records, it can take some time.</p>
  
  <p>EDIT:</p>
  
  <p>My solution was as follows:</p>
  
  <pre><code>    all_scores_in_time_period = UserScore
        .where("EXTRACT(MONTH FROM created_at) = ?", Date::MONTHNAMES.index(params[:month_control]))
        .where("EXTRACT(YEAR FROM created_at) = ?", params[:year_control])
        .select("DISTINCT ON(DATE(created_at), user_id) *")
        .order("DATE(created_at) desc")
  </code></pre>
  
  <p>This first filters by month/year, and then returns a list of users by their top score per user per day.</p>