SQL Distinct on One Column with NO ID

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

SELECT [Cust Reference], CustType, County
FROM CustInfo
WHERE [Cust Reference] LIKE "%20%" 
  AND [Cust Reference] LIKE "P1%" 
  AND CustType NOT LIKE "%expired%" 
  AND CustType <> ""
GROUP [Cust Reference], CustType, County;

But when I use above with a SELECT INTO and check for duplicates there are some.

I have looking at this post – SELECT DISTINCT on one column

But the CustInfo table has no ID column and it is not really possible to do a LIKE 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

SELECT DISTINCT t1.[Cust Reference]
FROM CustInfo AS t1
WHERE t1.[Cust Reference] LIKE "%20%" 
  AND t1.[Cust Reference] LIKE "C1%" 
  AND t1.CustType NOT LIKE "%expired%" 
  AND t1.CustType <> ""

But when I try to broaden this to include three columns as follows it does not work. I copied the CustInfo into CustInfo_Temp to work the join. What needs to be corrected in this query to extract the three columns with DISTINCT ONLY on [Cust Reference}?

SELECT t2.[Cust Reference], CustType, County
FROM 
    (SELECT DISTINCT t1.[Cust Reference]
     FROM CustInfo AS t1
     WHERE t1.[Cust Reference] LIKE "%20%" 
       AND t1.[Cust Reference] LIKE "P1%" 
       AND t1.CustType NOT LIKE "%expired%" 
       AND t1.CustType <> "") AS t2
JOIN CustInfo_Temp ON t1.[Cust Reference] = t2.[Cust Reference]

Answer

I’m not sure what you really want, but aggregation will guarantee one row per [Cust Reference]:

SELECT [Cust Reference], MAX(CustType), MAX(County)
FROM CustInfo
WHERE [Cust Reference] LIKE "%20%" AND
      [Cust Reference] LIKE "P1%" AND
      CustType NOT LIKE "%expired%" AND
      CustType <> ""
GROUP [Cust Reference];

Advertisement

Answer