Distinct

[rcuser]

Hi,

I'm pretty new to SQL but need to do the following in a query:

Select Disctinct(address)
FROM myTable

The problem is the "address" is made up of five table columns address1, address2, city, and zipcode. How would I incorporate this into the query?
Thanks,


Richard

[lilleman]

Hi,

Take a look at the CONCAT function.

Yours, Erik.

[swampBoogie]

Distinct is always applied to all columns in the result (so using concat will not make any difference).

select distinct adress1,adress2, city, zipcode from t

Why do write

distinct (adress)

?

distinct is not a function.

[rcuser]

Your right I actually mean't:

select distinct address
from myTable

But I'm still no better off

Any suggestions of how to select an address once when the address is split into five columns?

[Dean C]

So you want the address returned into one variable?

[swampBoogie]

Could you give some samples of your data and the expected result?

[rcuser]

I don't strictly want the address returned in one variable I just want to select only one exact address match i.e.

address1, address2, city, zipcode, country
1 high street, , london, abc 123, UK (selected)
2 high street, greenwich, london, abc 123, UK (selected)
1 high street, greenwich, london, abc 123, UK (selected)
1 high street, , london, abc 123, UK (not selected as matched result one)

Does this make sense?

[swampBoogie]

In that case the query i posted

select distinct adress1,adress2, city, zipcode from t

should be sufficient.

[rcuser]

OK thanks, I'll give it a whirl

[rcuser]

Thanks, that did work. However, I do have one last question that I can't quite figure out...

I also needed the ID number (passesID) for each of the records. Unfortunately when I included the passesID in the select statement it is using it within the distinct feature makeing each of the entries automatically unique.
i.e.
SELECT DISTINCT address1, address2, city, zipcode, country, passesID
FROM myTable

How do I get round this last bit?
Thanks,

Richard

[swampBoogie]

Which value of passesID within each distinct group do you want? Highest lowest?

select max(passesId),adress1,adress2, city, zipcode from t
group by adress1,adress2, city, zipcode

[rcuser]

Thanks thats worked a treat.