Ms-access – Select DISTINCT records with extra information

ms accessquery

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.

Best Answer

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.

Related Solutions

Multiple Distinct Columns

Assuming your RDBMS supports windowing functions, something like this will give you what you are asking for:

select * 
from( select dataentry.*, 
             row_number() over (partition by fname, mname, lname order by title) n
      from dataentry )
where n=1;

But note that I have arbitrarily chosen to order by title when discarding 'duplicates' - and even more arbitrarily chosen to overlook that if two matching rows also have matching titles the database will order them in an undefined and possibly unpredictable way. You will need to adjust that to meet your requirements.

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

UPDATE Now I see your error. The inner query uses aggregation, and takes out sizeRange which is not a column you aggregate on. So you only get "samples" of that column. Strictly speaking, your query is not valid SQL but MySQL allows it given a relaxed sql_mode.

So your query is inherently erroneous. Will see if I can help fix it.

ORIGINAL answer

My guess for you would be to check the value of group_concat_max_len.

It is by default just 1024, though you typically don't really want a limit for that.

The problem might be that you are only getting partial results, where, by chance or by order of evaluation, "S" and "XL" occupy first 1024 characters or more. I see no reason why "M" or "L" would not be there -- the GROUP_CONCAT doesn't do such distinctions.

So, try out:

SET group_concat_max_len := 1000000;

And execute your query again. If this works, make sure to set said param in you MySQL configuration file.

You may find my related post useful.

Best Answer

Related Solutions

Multiple Distinct Columns

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

Related Question