1

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. – a_horse_with_no_name Jan 20 '17 at 6:52
  • I'm using MS Access. – CurtisD Jan 20 '17 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 '17 at 10:33
  • I want to select just one customer per city. It doesn't matter which one. – CurtisD Jan 20 '17 at 19:53
1

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

SELECT c.* 
FROM Customers AS c
  INNER JOIN
    ( SELECT CustCity, MIN(CustID) AS CustID
      FROM Customers
      GROUP BY CustCity
    ) AS m
  ON (  m.CustCity = c.CustCity
    AND m.CustID = c.CustID
     ) ;
0
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 '17 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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