Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one row. Here is the basic query I have so far:

Group   Fname   Lname   Address

So my first attempt was:

SELECT DISTINCT Customers.[Address 1]
FROM Customers

This works as I wanted and returns only unique addresses. However I also need the group, fname and lname fields. When I add these in I lose the DISTINCT functionality as often customers in the same household will have different Lname and grouping.

Whats the best way to select the data I need but still use DISTINCT on the [Address 1] field.

share|improve this question
    
And you want one random row from the many in the same address? –  ypercube Jun 27 '12 at 11:23
    
Yes one random row from each address is fine, either that or pick the row with the oldest [Order Date] –  Alex Jun 27 '12 at 11:31
add comment

1 Answer

up vote 1 down vote accepted
SELECT c.*
FROM 
      Customers AS c
   INNER JOIN
      ( SELECT [Address 1], MIN([Order Date]) AS min_od
        FROM Customers
        GROUP BY [Address 1]
      ) AS grp
         ON  grp.[Address 1] = c.[Address 1]
         AND grp.min_od = c.[Order Date] ;

This may still give you two rows for an address, if you have two rows with the same minimum [Order Date] for an address.

share|improve this answer
    
You could use min(FName + LName) instead of min([Order Date]) (hopefully, FName+LName is unique for one address :-) ) –  Frank Schmitt Jun 27 '12 at 12:59
    
@FrankSchmitt: If uniqueness is required, then he can use MIN(pk) –  ypercube Jun 27 '12 at 13:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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