title: ORDER BY columns that are sometimes empty using Active Record & Rails
body: <p>In my rails app (using postgresql), I'm trying to compose an Active Record query to find a group of volunteer records and then order them by <code>first_name</code>, then <code>last_name</code>, then <code>email</code>. Additionally, <code>first_name</code> and <code>last_name</code> may be <code>null</code> (either both will be <code>null</code> or both will be not <code>null</code>). For example, I would want to following list to be sorted thusly:</p>
  <ol>
  <li>Volunteer [first_name: 'Alex', last_name: 'Diego', email: 'a.diego@person.com']</li>
  <li>Volunteer [first_name: <code>null</code>, last_name: <code>null</code>, email: 'cxxr@person.com']</li>
  <li>Volunteer [first_name: 'Josh', last_name: 'Broger', email: 'broger@person.com']</li>
  <li>Volunteer [first_name: 'Josh', last_name: 'Broger', email: 'jcool@person.com']</li>
  <li>Volunteer [first_name: 'Josh', last_name: 'Kenton', email: 'aj@person.com']</li>
  </ol>
  <p>Originally, I had the following code:</p>
  <pre><code>Volunteer.joins(:volunteer_lists).
    where(&quot;(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
    (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')&quot;,
    self.organization.id, collaboratives).uniq.
    order(:first_name, :last_name, :email)
  </code></pre>
  <p>This code works except the results are grouped by volunteers with <code>first_name</code> &amp; <code>last_name</code> first, other volunteers with only <code>email</code> last (so in the example list above, volunteer #2 would be last). The answer to <a href="https://stackoverflow.com/questions/8120953/order-by-with-columns-that-are-sometimes-empty">this helpful post</a> indicates that I should use a <code>COALESCE()</code> function in the <code>ORDER BY</code> part of the statement to get the results I want. Awesome! So I updated my code to the following:</p>
  <pre><code>Volunteer.joins(:volunteer_lists).
    where(&quot;(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
    (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')&quot;,
    self.organization.id, collaboratives).uniq.
    .order('COALESCE(&quot;volunteers&quot;.&quot;first_name&quot;, &quot;volunteers&quot;.&quot;email&quot;) ASC, COALESCE(&quot;volunteers&quot;.&quot;last_name&quot;, &quot;volunteers&quot;.&quot;email&quot;) ASC, &quot;volunteers&quot;.&quot;email&quot; ASC')
  </code></pre>
  <p>The problem is that this code now returns</p>
  <pre><code>PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  </code></pre>
  <p>Using <code>to_sql</code> on both versions of the code, I find they are exactly the same except for the addition of the <code>COALESCE()</code> function.</p>
  <p><code>to_sql</code> of original, working, code:</p>
  <pre><code>SELECT  &quot;organizations&quot;.* FROM &quot;organizations&quot; WHERE &quot;organizations&quot;.&quot;id&quot; = $1 LIMIT 1  [[&quot;id&quot;, 1]]
    =&gt; &quot;SELECT DISTINCT \&quot;volunteers\&quot;.* FROM \&quot;volunteers\&quot; INNER JOIN \&quot;volunteer_list_connectors\&quot; ON \&quot;volunteer_list_connectors\&quot;.\&quot;volunteer_id\&quot; = \&quot;volunteers\&quot;.\&quot;id\&quot; INNER JOIN \&quot;volunteer_lists\&quot; ON \&quot;volunteer_lists\&quot;.\&quot;id\&quot; = \&quot;volunteer_list_connectors\&quot;.\&quot;volunteer_list_id\&quot; WHERE ((volunteer_lists.organizer_id = 1 AND organizer_type = 'Organization') OR\n      (volunteer_lists.organizer_id IN (1) AND organizer_type = 'Collaborative'))  ORDER BY \&quot;volunteers\&quot;.\&quot;first_name\&quot; ASC, \&quot;volunteers\&quot;.\&quot;last_name\&quot; ASC, \&quot;volunteers\&quot;.\&quot;email\&quot; ASC&quot;
  </code></pre>
  <p><code>to_sql</code> of updated code (the only difference is after <code>ORDER BY</code>):</p>
  <pre><code>SELECT  &quot;organizations&quot;.* FROM &quot;organizations&quot; WHERE &quot;organizations&quot;.&quot;id&quot; = $1 LIMIT 1  [[&quot;id&quot;, 1]]
    =&gt; &quot;SELECT DISTINCT \&quot;volunteers\&quot;.* FROM \&quot;volunteers\&quot; INNER JOIN \&quot;volunteer_list_connectors\&quot; ON \&quot;volunteer_list_connectors\&quot;.\&quot;volunteer_id\&quot; = \&quot;volunteers\&quot;.\&quot;id\&quot; INNER JOIN \&quot;volunteer_lists\&quot; ON \&quot;volunteer_lists\&quot;.\&quot;id\&quot; = \&quot;volunteer_list_connectors\&quot;.\&quot;volunteer_list_id\&quot; WHERE ((volunteer_lists.organizer_id = 1 AND organizer_type = 'Organization') OR\n      (volunteer_lists.organizer_id IN (1) AND organizer_type = 'Collaborative'))  ORDER BY COALESCE(\&quot;volunteers\&quot;.\&quot;first_name\&quot;, \&quot;volunteers\&quot;.\&quot;email\&quot;) ASC, COALESCE(\&quot;volunteers\&quot;.\&quot;last_name\&quot;, \&quot;volunteers\&quot;.\&quot;email\&quot;) ASC, \&quot;volunteers\&quot;.\&quot;email\&quot; ASC&quot;
  </code></pre>
  <p>I tested trying my new code without <code>.uniq</code> (to remove the <code>DISTINCT</code> part of the sql) and when I do this the new code runs without error, however the results are NOT sorted properly: they are sorted the same way as my original code is (the code without <code>COALESCE()</code>).</p>
  <p>I imagine that there is a syntax error that I've committed, but I can't figure out what it is (or perhaps I'm wrong and <code>COALESCE()</code> is not the proper solution to my problem).</p>
  <p>Any help is GREATLY appreciated!!</p>
  <h1>UPDATE &amp; ANSWER</h1>
  <p>After getting invaluable help from <a href="https://stackoverflow.com/users/802618/kristj%C3%A1n">Kristján</a> and his answer below, I solved what turned out to be multiple problems:</p>
  <ol>
  <li>When you add <code>.uniq</code> to an ActiveRecord query, it adds <code>DISTINCT</code> to the sql that gets sent to the database. <code>SELECT DISTINCT</code> has some stricter requirements than simply <code>SELECT</code>. As pointed out by Kristján and <a href="https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by">described in more detail in this S.O. answer</a>, the <code>DISTINCT</code> expression(s) must match the leftmost <code>ORDER BY</code> expression(s). When I updated <code>.order()</code> with my sql fragment including <code>COALESCE()</code>, I also needed to add a matching sql fragment to the <code>SELECT </code>part of the statement with <code>.select()</code>.</li>
  <li>1 above just removes the error I was getting. At that point, my query was running but the results were being sorted the same as they were before using <code>COALESCE()</code>. Kristján provides a proper description in his answer below, but turns out my query was running correctly, its just that <code>COALESCE()</code> sorts anything uppercase before anything lowercase. so &quot;Z&quot; will be sorted in front of &quot;a&quot;. This problem can be solved by adding a function to convert the <code>COALESCE()</code> fields to lowercase using <code>LOWER()</code>.</li>
  </ol>
  <p><strong>Here is my answer:</strong></p>
  <pre><code>    Volunteer.select('LOWER(COALESCE(&quot;volunteers&quot;.&quot;first_name&quot;, &quot;volunteers&quot;.&quot;email&quot;)), LOWER(COALESCE(&quot;volunteers&quot;.&quot;last_name&quot;, &quot;volunteers&quot;.&quot;email&quot;)), LOWER(&quot;volunteers&quot;.&quot;email&quot;), &quot;volunteers&quot;.*').
        joins(:volunteer_lists).
        where(&quot;(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
        (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')&quot;,
        self.organization.id, collaboratives).uniq.
        order('LOWER(COALESCE(&quot;volunteers&quot;.&quot;first_name&quot;, &quot;volunteers&quot;.&quot;email&quot;)) ASC, LOWER(COALESCE(&quot;volunteers&quot;.&quot;last_name&quot;, &quot;volunteers&quot;.&quot;email&quot;)) ASC, LOWER(&quot;volunteers&quot;.&quot;email&quot;) ASC')
  </code></pre>
  <p><strong>Note:</strong></p>
  <p>My answer above actually created another problem when I later call <code>.count</code> on the query. <code>.count</code> breaks because of the custom <code>.select()</code> fragment I've added. To solve this, I needed to add a custom <code>volunteers_count</code> method to the <code>User</code> model that didn't make use of the <code>.select()</code> fragment.</p>