title: SQL Distinct on One Column with NO ID
body: <p>I have a problem where I need to select three columns from a table in Access to populate another table but it needs to be distinct on one column ONLY.  I tried the GROUP BY as follows</p>
  <pre><code>SELECT [Cust Reference], CustType, County
  FROM CustInfo
  WHERE [Cust Reference] LIKE &quot;%20%&quot; 
    AND [Cust Reference] LIKE &quot;P1%&quot; 
    AND CustType NOT LIKE &quot;%expired%&quot; 
    AND CustType &lt;&gt; &quot;&quot;
  GROUP [Cust Reference], CustType, County;
  </code></pre>
  <p>But when I use above with a SELECT INTO and check for duplicates there are some.</p>
  <p>I have looking at this post - <a href="https://stackoverflow.com/questions/966176/select-distinct-on-one-column">SELECT DISTINCT on one column</a></p>
  <p>But the <code>CustInfo</code> table has no ID column and it is not really possible to do a <code>LIKE</code> as the post above describes. I have tried the following and it gives me what I think is the correct output and count on one column</p>
  <pre><code>SELECT DISTINCT t1.[Cust Reference]
  FROM CustInfo AS t1
  WHERE t1.[Cust Reference] LIKE &quot;%20%&quot; 
    AND t1.[Cust Reference] LIKE &quot;C1%&quot; 
    AND t1.CustType NOT LIKE &quot;%expired%&quot; 
    AND t1.CustType &lt;&gt; &quot;&quot;
  </code></pre>
  <p>But when I try to broaden this to include three columns as follows it does not work. I copied the <code>CustInfo</code> into <code>CustInfo_Temp</code> to work the join.  What needs to be corrected in this query to extract the three columns with DISTINCT ONLY on [Cust Reference}?</p>
  <pre><code>SELECT t2.[Cust Reference], CustType, County
  FROM 
      (SELECT DISTINCT t1.[Cust Reference]
       FROM CustInfo AS t1
       WHERE t1.[Cust Reference] LIKE &quot;%20%&quot; 
         AND t1.[Cust Reference] LIKE &quot;P1%&quot; 
         AND t1.CustType NOT LIKE &quot;%expired%&quot; 
         AND t1.CustType &lt;&gt; &quot;&quot;) AS t2
  JOIN CustInfo_Temp ON t1.[Cust Reference] = t2.[Cust Reference]
  </code></pre>