Select a distinct value amoung many 1

[Zarcom]

I would like to know how to do a select that has one of the columns specified as distinct.

Select Distinct Col1 col2 col3 col4
from table

doesn't work because I still get duplicates in col3. I would like to get all of the columns but to eliminate any row that has a duplicate value in col3

That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[sunila7]

Hi,

Is this wht u r looking for....

Select Col1, col2, max(col3), col4
from tble
group by col1,col2,col4

Sunil

 

[Zarcom]

Nope col3 is not a number column it is a string column and I dont' want the max value out of it just to eliminate rows with duplicates in this column

That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[sunila7]

Hi,

Can u provide some sample data and the result u r looking for.....


Sunil

 

[Zarcom]

yup My db design probably sucks so if there is a better way I should have this feel free to tell me.

I have a table called location it contains the columns
Country, Province, City, ID

The three columns make up a unique location. Now on one of the pages I am building I want to display a drop down of provinces for people to select from. The thing is I wanted to read more than just the province field. I don't want the province of say Alberta to appear twice in the drop down but I still want to select all the fields above.

Hopefully that clears it up a bit.

That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[JamesLean]

I'm not quite sure of the logic behind what you want to do (if you just want a list of the provinces why not just

SELECT DISTINCT province

?) but if you need to return data from other columns but keep distinct provinces then you need to decide which duplicate row you want to keep.

Say you had three rows for a particular province:

CountryA | Province1 | City 1 | 1
CountryA | Province1 | City 2 | 2
CountryA | Province1 | City 3 | 3

Now you say you only want to return one of those rows. Which one? The one with the lowest ID? The one with the MAX(city)? You need to decide your rules to be able to build a query.

--James