select distinct question

[lution]

I have a query to pull fields to create envelopes so it has name, address, etc. I did this as a select distinct to make sure I only got one envelope per person. Now my users want to be able to apply some criteria to the list, example past due. When I added the criteria fields, it messed up my distinct since the due date is different for each record.

Example that used to give me one envelope per name/address combination:
Select distinct name, address from mytable;

New example that essentially gives me one envelope for every record:
Select distinct name, address, duedate from mytable where date > duedate;

I orginally tried:
Select distinct name, address from my table where date > duedate;

but all the fields in the where need to be part of the select clause.

I'm having a bear of a time figuring out how to form this query and I've probably worked myself up enough that I'm making it way more difficult that it needs to be.

Thanks
Lution

 

[pbaldy]

Try

Select name, address from my table where date > duedate
GROUP BY name, address

 

[lution]

Thanks pbaldy. I tried the group by but couldn't get it to come out just the way I wanted. I ended up creating a 2nd query with the select distinct with just the name/address fields in it and use the 1st query with the where fields as the input. Seems to be working fine. No idea what the performance is going to be on larger datasets.

Safe travels
Lution