SELECT DISTINCT Problem

[bergy]

Hello MS SQL experts, I'm trying to reorganize some data for a friend and I'm running into this problem. Currently he has some duplicate rows that I need to get rid of - only one of the columns has duplicates and then there is a unique primary key.

The table looks like this:

id    color
---   ------
 3     red
 4     red
 5     blue
 6     green
 7     red
 8     blue
 9     green

I'm trying to eliminate the duplicates and tie the color to one id. This ties in with some software so I was planning on doing it in 2 steps. First, return some results with no duplicate color and their corresponding id. Then run a quick delete statement - something like "DELETE FROM table WHERE id NOT 3 AND id NOT 5 AND id NOT 6".

I tried playing around with SELECT DISTINCT but I can't seem to get the id to return with the distinct results. Examples:

SELECT DISTINCT color FROM table
Returns:

color
------
 red
 blue
 green

With no ID field

SELECT DISTINCT color, id FROM table
Returns my original result (since all ids are distinct)

and I'm looking for a way to arrive at this:

id    color
---   ------
 3     red
 5     blue
 6     green
 

Any help is appreciated - using SQL Server 2000 and MSDE as testing environment.

[mabubakarpk]

Yes you can get your solution

 Select max(id) as id ,   color  from tablename group by Color
 

[bergy]

Thanks a bunch mabuakarpk! I've never used min or max before, I'll have to look them up and see what else they can be used for. You are a life saver!