I have a question about using the DISTINCT keyword in SQL. I have a table named Customers. Each record contains 3 fields: CustID, CustName, CustCity. 5 records show a CustCity of "Chicago".

I want the SQL results to show just one record for each unique city name. Also, I just want to see the CustID and the CustName from each record.

I tried this but got a syntax error:

SELECT CustID, CustName DISTINCT CustCity 
FROM Customers
  • 1
    Which DBMS are you using? DISTINCT applies to all columns in the select list and must be written before all columns. Jan 20, 2017 at 6:52
  • I'm using MS Access.
    – CurtisD
    Jan 20, 2017 at 9:46
  • You want "the CustID and the CustName from each record"? Which CustID and CustName exactly? If there are multiple customers per city, you must define which one you want to show if you want each city reference to show just once. I can't help wondering, though, why you need customer names if your query is about distinct cities.
    – Andriy M
    Jan 20, 2017 at 10:33
  • I want to select just one customer per city. It doesn't matter which one.
    – CurtisD
    Jan 20, 2017 at 19:53

2 Answers 2


MS-Access lacks window functions, so you'll need a subquery solution. I think this would work:

FROM Customers AS c
    ( SELECT CustCity, MIN(CustID) AS CustID
      FROM Customers
      GROUP BY CustCity
    ) AS m
  ON (  m.CustCity = c.CustCity
    AND m.CustID = c.CustID
     ) ;
select t.* from 
    select custId,custName,CustCity,row_number() over (partition by custcity order by custcity) r from customers
) t
where r=1
  • 2
    The OP is using MS Access. This solution won't work in Access.
    – Andriy M
    Jan 20, 2017 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.