SQL distinct clause

[Gunslinger08]

Is it possible to have the distinct clause look at only one column, when selecting multiple columns?

For example, if I have a select command like so:
SELECT DISTINCT t_Address.chrCountry AS EnteredCountry, t_Address.*
FROM t_Address INNER JOIN
Country ON t_Address.chrCountry <> Country.nvcCountry

This returns every row that is completely distinct from another row. However, what I want is every row with a distinct t_Address.chrCountry. How can I achieve this?

 

[Jeraden]

I think the answer is no, but I don't understand what you are trying to do. What do you expect the above to return? give an example.

Also, why do you have <> in your join instead of =?

 

[Zontor]

Need more details....off the top of my head I'd do a GROUP BY with a HAVING clause of some type....

 

[Gunslinger08]

Say I have valid countries listed in the Country table, and addresses stored in the t_Address table. I want to find all entries in t_Address's chrCountry field that don't exist in Country.nvcCountry and return 1 example row for each of these invalid entries. For example, there might be 500 rows in t_Address that have "U.S.A." in the chrCountry field, but I just want to return 1 of them, as an example.

 

[b4u]

I attached some code ... hope it's what you're looking for ... it'll display all countries that don't have a match on the "Country" table, and displays them only once.

 

[Jeraden]

Here's a query that works in Oracle. I don't know mysql or whatever database you are using, so you'll have to figure out the equivalent syntax in your language.

select a.*
from t_address a
where (a.chrCountry, a.rowid) in
(
select b.chrCountry, min(b.rowid)
from t_address b
where b.chrCountry not in (select nvcCountry from Country)
group by b.chrCountry)

Kind of takes what the guy above had, but will return 1 example row from t_address for each country that doesn't exist in the other table.
Basically makes use of the rowid (which is a hidden identifier in Oracle), but you can use the primary key of the t_address table as well, I just didn't know what field that is for you.

I tested it on a table here and it works, assuming I didn't typo something changing the field/table names.