create table after eliminating record with duplicates on one column

I have table like this:

department  job_description       active_code
1                  worker1                          32
1                  worker2                          33
1                  worker3                          34
2                  worker3                          35
2                  worker3                          36
2                  worker4                          37
                             
I need to eliminate records who have duplicates on the "job_description" column , to leave only one worker3 record on the table (doesn't care which one) , the output should be  like this:

department  job_description       active_code
1                    worker1                         32
1                    worker2                         33
1                    worker3                         34
2                     worker4                        37
 
only one worker3
center1010Asked:
Who is Participating?
 
tigin44Commented:
the above post will give you the result set you demand... and this one do replace your table with the new one...
SELECT MIN(department) AS department, job_description, MIN(active_code) AS active_code INTO yourNewtable
FROM yourTable  
GROUP BY job_description

DROP TABLE yourTable

SELECT * INTO yourTable
FROM yourNewtable

Open in new window

0
 
tigin44Commented:
try this
SELECT MIN(department) AS department, job_description, MIN(active_code) AS active_code
FROM yourTable  
GROUP BY job_description

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT *
FROM yourTable t
WHERE Active_code = (SELECT MIN(active_code) from yourTable where job_description = t.job_description )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.