In this statement:

"SELECT DISTINCT Address1 FROM Address"

I would also like to see a couple other fields like AddressID and City, but
want only the Address1 to be distinct. How would I write this?

TIA

You should begin by deciding how to choose the AddressID and City
values when there are multiple rows with the same Address1 value.
Once you do that, it will be possible to write a query.  Otherwise,
you need an ANY_BUT_I_COULD_CARE_LESS_WHICH()
function, and there isn't one.

Come up with a precise statement of what you want your
query to return, and ask again if that doesn't get you far enough.
Here are some tips on posting that will increase the chance
of geting a useful answer:

http://www.aspfaq.com/etiquette.asp?id=5006

Steve Kass
Drew University

smk23 wrote:

Show quoteHide quote

Assuming the AddressId and City come from the same table, you can use the
following sub query:

SELECT DISTINCT t1.Address1 as Address1,
  (select top 1 AddressId from Address where Address1 = t1.Address1) as
AddressId,
  (select top 1 City from Address where Address1 = t1.Address1) as City
FROM Address t1

But that's not so efficient, if you deal with lot of records, you better do
outer join to the Address table itself.


Show quoteHide quote

Return One Row Per User, not TOP(1) HELP Please
Lookup tables
Cursor Update problem
case and order by problem?
table locking