[AccessD] First in a group

Hi John

With a subquery. No smart methods for this as far as I know.

/gustav

>>> jwcolby at colbyconsulting.com 13-09-2007 16:06 >>>
I have a set of address records where there are several people at the
address.  I need to send only one piece of mail to that address, the person
doesn't matter.

John Colby, Some Street, SomeCity, SomeState, SomeZip
Mary Colby, Some Street, SomeCity, SomeState, SomeZip
John Doe, Some Other Street, Some Other City, Some Other State, Some Other
Zip
Mary Doe, Some Other Street, Some Other City, Some Other State, Some Other
Zip

I don't care whether I send to Mary or John, I only care that only a single
piece of mail goes to each address.

Select distinct of course acts on the entire row and so returns all 4 names.

I do have an address hash that generates a hash string for just the address
part.

So it seems like something like a group by address hash and then a top1 is
the right direction, but of course it has to be the TOP 1 in each GROUP.  

I could (and might have to to get this out) write a little VBA routine to do
this but I would rather use a SQL solution.  

So how is this done in SQL.  

John W. Colby
Colby Consulting
www.ColbyConsulting.com