[AccessD] First in a group

A subquery for each address in the Qry.SQL ...

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com

----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Thursday, September 13, 2007 10:23 AM
Subject: Re: [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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com