I have a location table that contains all North American zip codes, the
city, county, state etc.. each record has a GUID. My problem is that
several places in the US you will have a single city and zip code that
crosses a county line. So if you need to match this table to another
in a select statement using the zip and city you can pull 2 records
that are duplicated for everything except the county.

Now for my use I don't care about the county, only want 1 record but I
need one of the 2 guids that is generated by this select statement and
really don't care which one.

The only way that I can think to do this is by grouping on the zip and
city. My problem is that I need to grab one of the GUIDs and it is not
permissible to use an aggregate on one a GUID.

I really don't want to have to pull the county in to match to this
table as it is not really a relevant field for what I am doing and it
would cause several hours of additional work to the front end entry
screen. Also unfortunately the data in my table needs to remain as it
is because it is linked with sales tax rate tables.
And yes 2 people might be the same city and zip code, maybe even next
door neighbors, but being in different counties they might pay
different sales tax rates.

Any ideas?

Why are you using GUIDs as a key? It seems like you have a flawed model
to start with if (zip,city) has meaning to you as an entity but it
isn't defined in its own table. How about creating a new table keyed on
(zip,city)?

If you intend to persist "one of the GUIDs" for a given (zip,city) but
you don't care which then you have a potential integrity problem. A
(zip,city) with an arbitrary country will be indistinguishable from a
known(zip,city,county) - a different entity.

You didn't actually specify an alternate key but assuming (zip, city,
county) is a key in your table then try this to get the GUID:

SELECT zip, city, guid
FROM YourTable AS T
WHERE county =
(SELECT MIN(county)
FROM YourTable
WHERE zip = T.zip
AND city = T.city)

This is problematic in itself. If you add a new county (don't they
change sometimes?) you may get a different GUID for the same (zip,
city). I don't think there's a reliable answer to that unless you fix
the design. Ditch the GUIDs too. I don't see any justification for them
in a address/geography table.

--
David Portas
SQL Server MVP
--

Unfortunatly what I am creating some small apps around an ERP package
to push data into this ERP package. The people who designed this
package use the GUIDs significantly for as their keys and to link
different tables.
So I have specific parameters that I have to work within so that the
data that I post will interact properly with my ERP system.

I probably should add the county to the entry form and then match up
zip, city and county. However right now I just have it as a basic
entry form that posts to a table and then the SQL scripts pick it up
and move all the information around. If I were to do this and have it
work reliably I would really need to change it to a db driven form so
that they could enter the zip and pull up all relevant cities and
counties. That would be the proper way to do it and I probably will at
some point. But for right now I need a good work around that won't
really cause any business level issues. So I will give this a shot.
Thanks,
Mike