asked on

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

tigin44

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

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

SELECT *
FROM yourTable t
WHERE Active_code = (SELECT MIN(active_code) from yourTable where job_description = t.job_description )