title: left join and group by
body: <p>I have table like below two, I want to list location row base on been used in task table, and dont duplicate location.id, also order by column in task table.</p>
  
  <p>I'm not sure how to solve it use group by or other method?<br>
  (I also check distinct before, but it limit select specific column, seems not what I want..)<br>
  how to solve it ?   </p>
  
  <pre><code>location
  id | status | ...
  1  | ...
  2  | ...
  
  id SERIAL NOT NULL
  
  task
  id | location_id | create_date | start_date | ...
  1  | 1           | ...
  2  | 1           | ...
  3  | 2           | ...
  
  id SERIAL NOT NULL
  location_id integer DEFAULT NULL
  create_date timestamp without time zone NOT NULL  
  start_date date NOT NULL
  </code></pre>
  
  <p>need output result </p>
  
  <pre><code>location  order by task.create_date
  id | ...
  1  | ...
  2  | ...
  </code></pre>
  
  <p>query  </p>
  
  <p>I add select t.location_id it works to count, but I still have problem in select rows</p>
  
  <p>count works</p>
  
  <pre><code>SELECT count(l.*)
     AS total_row_count 
     FROM location l
           LEFT JOIN (
             SELECT t.location_id
             FROM task t
             GROUP BY t.location_id
             ) t ON t.location_id  = l.id
          WHERE l.status = ANY($1::int[])
  </code></pre>
  
  <p>select </p>
  
  <pre><code>SELECT
    l.* 
    FROM location l 
          LEFT JOIN (
            SELECT t.location_id, t.create_date
            FROM task t
            GROUP BY t.location_id
            ) t ON t.location_id  = l.id
         WHERE l.status = ANY($1::int[]) 
         ORDER BY t.create_date desc NULLS LAST, 
         l.name asc NULLS LAST, l.id desc NULLS LAST OFFSET $2 LIMIT $3
  </code></pre>
  
  <p>error:  </p>
  
  <blockquote>
    <p>column "t.create_date" must appear in the GROUP BY clause or be used
    in an aggregate function SELECT t.create_date, t.location_id</p>
  </blockquote>