title: GroupingError: ERROR: column " " must appear in the GROUP BY clause or be used in an aggregate function
body: <p>I am trying to create a list of unique patients who have left comments.  The code words fine until I upload to heroku where it doesnt work in postgresql.</p>
  
  <p>This is my Ruby .erb code to create the list:</p>
  
  <pre><code>&lt;% @comments.group(:patient_id).each_with_index do |comment, index| %&gt;
      &lt;div class="profile-post color-one"&gt;
         &lt;span class="profile-post-numb"&gt;&lt;%= index+1 %&gt;&lt;/span&gt;
          &lt;div class="profile-post-in"&gt;
              &lt;h3 class="heading-xs"&gt;&lt;a&gt;&lt;%= link_to "#{comment.patient.first_name} #{comment.patient.last_name}", comment_path(comment) %&gt;&lt;/a&gt;&lt;/h3&gt;
              &lt;p&gt;Lastest comment from &lt;%= time_ago_in_words(comment.created_at) %&gt; ago&lt;i class="pull-right"&gt;&lt;%= link_to "Edit", edit_comment_path(comment) %&gt; &lt;%= link_to "Delete", comment_path(comment), method: :delete, data: {confirm: 'Are you sure you want to delete'} %&gt;&lt;/i&gt;&lt;/p&gt;
          &lt;/div&gt;
      &lt;/div&gt;
  &lt;% end %&gt;
  </code></pre>
  
  <p>@comments is defined in the controller as:</p>
  
  <pre><code>def index
    @comments = current_clinician.comments.order("created_at desc")
  end
  </code></pre>
  
  <p>heroku logs give me this as the error message:</p>
  
  <blockquote>
  <pre><code>PG::GroupingError: ERROR:  column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function
  
  LINE 1: SELECT "comments".* FROM "comments"  WHERE  comments"."clini...
                 ^
  SELECT "comments".* *FROM "comments"  WHERE "comments"."clinician_id" = $1 GROUP BY patient_id  ORDER BY created_at desc
  </code></pre>
  </blockquote>
  
  <p>I have tried the solutions from a other SO questions, such as <a href="https://stackoverflow.com/questions/20942477/groupingerror-error-column-must-appear-in-the-group-by-clause-or-be-used-in-an">20942477</a>. Which said that I should add the field comments.id to my group clause:</p>
  
  <pre><code>&lt;% @comments.group(:patient_id, :"comments.id").each_with_index do |comment, index| %&gt;
  </code></pre>
  
  <p>This gets rid of the error on heroku but defeats the purpose of the group command - it no longer only shows unique patients but instead lists all of them.</p>
  
  <p>I also tried the solutions from  <a href="https://stackoverflow.com/questions/1780893/postgres-partial-column-in-select-group-by-column-must-appear-in-the-group-b">1780893</a>.  Which said that I should change the ORDER BY:</p>
  
  <pre><code>@comments = current_clinician.comments.order("substring(created_at,1.8) desc")
  </code></pre>
  
  <p>Which gives this error locally:</p>
  
  <blockquote>
    <p>SQLite3::SQLException: no such function: substring: SELECT
    "comments".* FROM "comments" WHERE "comments"."clinician_id" = ? ORDER
    BY substring(created_at,1.8) desc</p>
  </blockquote>
  
  <p>I realize this is a common problem, I am inexperienced with SQL so I am having trouble getting the code so it will work in both my development and production environments.  The answers I have read on SO aren't using Ruby to get SQL and go over my experience level.</p>