How to Use SELECT DISTINCT

Question

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

Answer 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
     ) ;

Answer 2

select t.* from 
(
    select custId,custName,CustCity,row_number() over (partition by custcity order by custcity) r from customers
) t
where r=1