469,625 Members | 1,133 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

SELECT DISTINCT Problem

bergy
89
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:

Expand|Select|Wrap|Line Numbers
  1. id    color
  2. ---   ------
  3.  3     red
  4.  4     red
  5.  5     blue
  6.  6     green
  7.  7     red
  8.  8     blue
  9.  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:
Expand|Select|Wrap|Line Numbers
  1. color
  2. ------
  3.  red
  4.  blue
  5.  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:

Expand|Select|Wrap|Line Numbers
  1. id    color
  2. ---   ------
  3.  3     red
  4.  5     blue
  5.  6     green
  6.  
Any help is appreciated - using SQL Server 2000 and MSDE as testing environment.
Mar 6 '07 #1
2 2891
Yes you can get your solution

Expand|Select|Wrap|Line Numbers
  1.  Select max(id) as id ,   color  from tablename group by Color
  2.  
Mar 6 '07 #2
bergy
89
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!
Mar 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Martin Feuersteiner | last post: by
2 posts views Thread by mfyahya | last post: by
9 posts views Thread by Kelvin | last post: by
2 posts views Thread by ray well | last post: by
2 posts views Thread by johnhanis | last post: by
22 posts views Thread by MP | last post: by
5 posts views Thread by movieking81 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.