title: select distinct id when using left join and order by on two tables
body: <p>I have two tables and I am going to get <strong>distinct id from first one</strong> based on a  complex <strong>order by</strong> clauses on both tables. I'm confused how to use <strong>distinct</strong> or <strong>groub by</strong> because both of them prevent using <strong>order by</strong> on other columns. </p>
  
  <p>My code using distinct:</p>
  
  <pre><code>select distinct(shops.id) from shops 
  left join expiration on shops.id=expiration.shopid 
  order by 
    shops.grade desc,
    expiration.startdate asc,
    expiration.enddate desc,
    shops.id
  </code></pre>
  
  <blockquote>
    <p>ORDER BY items must appear in the select list if SELECT DISTINCT is
    specified</p>
  </blockquote>
  
  <p>My code using group by:</p>
  
  <pre><code>select max(shops.id) as shopid from shops 
  left join expiration on shops.id=expiration.shopid
  group by shopid
  order by 
    shops.grade desc,
    expiration.startdate asc,
    expiration.enddate desc,
    shops.id
  </code></pre>
  
  <blockquote>
    <p>Column "shops.grade" is invalid in the ORDER BY clause because it is
    not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>