create table after eliminating record with  duplicates on one column

Posted on 2009-12-24
Medium Priority
Last Modified: 2012-05-08
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
Question by:center1010
  • 2
LVL 26

Expert Comment

ID: 26119505
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

LVL 26

Accepted Solution

tigin44 earned 2000 total points
ID: 26119521
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

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26119568
FROM yourTable t
WHERE Active_code = (SELECT MIN(active_code) from yourTable where job_description = t.job_description )

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question