I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle initial is sometimes present).

I want to insert unique values, in alphabetical order, into a new table (see 'target table' for desired results). I don't care which version of someone's name is inserted into my target table...what I want is one truly distinct value per person in my target table sorted alphabetically by last name.

Source Table:

Emp_ID  Name
123 Jones, John
123 Jones, John P
123 Jones, John P.
456 Lewis, Jerry
456 Lewis, Jerry L
456 Lewis, Jerry L.
789 Hewitt, Jennifer
789 Hewitt, Jennifer L

Target table (desired results):

Emp_ID  Name
789 Hewitt, Jennifer
123 Jones, John
456 Lewis, Jerry L
  • Do all names have a comma between the last name and first name? Do they all have a last name and a first name? And in that order? Oct 24 '12 at 21:06
  • That is correct, there is always a comma between the last name and the first name. However, I was hoping i could use the Emp_ID column to get my unique list and then bring in the name associated with that ID... problem is that in many cases i have multiple names (albeit the same person) associated with that ID. --And yes, it's always: LastName, FirstName sometimes it's LastName, FirstName, MI
    – New-To-SQL
    Oct 24 '12 at 21:08
  • 2
    Ah, the EmpID is the same, just noticed. Yes, you can use that. INSERT INTO TargetTable (Emp_ID, Name) SELECT Emp_ID, MIN(Name) FROM SourceTable GROUP BY Emp_ID ; Oct 24 '12 at 21:09
  • That worked!!!! :) Thank you so much!!! I should have posted the question hours ago instead of wasting so much time! Thanks again! Have a great night! :)
    – New-To-SQL
    Oct 24 '12 at 21:14
  • A side note re "target table sorted alphabetically": tables don't have an order - they are sets, not spreadsheets :) Jul 27 '16 at 10:42

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names:

INSERT INTO TargetTable 
  (Emp_ID, Name) 
FROM SourceTable 

And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on the disk but that is no guarantee for the order of retrieval).

You can get the data from the target table with a query afterwards and if you want them ordered, you can (and should) define the order you like (and a different one if you like, every time you query it):

SELECT Emp_ID, Name 
FROM TargetTable 
  • 1
    +1 Although I would have gone for MAX() to keep as much information as possible; that's assuming more is better
    – 孔夫子
    Oct 24 '12 at 23:52
  • also +1. Depending on how the data was entered it may be best to use the first or last entry for each EmpID or it might be best to use the one that has the first name and middle initial. Without more info its just speculation. I would certainly mark this answer as correct.
    – Hannah Vernon
    Oct 25 '12 at 3:31
  • what I want to know is how does one end up with this problem in the first place. correct ID with incorrect text field? Although I guess that is what the OP is trying to fix by normalization.
    – Hannah Vernon
    Oct 25 '12 at 3:36
  • the source system is a user input system... so, the employee ID is entered correctly (most of the time) but the employee name can be entered differently depending on how much info the person entering the data enters (i.e. includes middle initial or not). I have no control over the source system. I'm just trying to clean up the data prior to actually using it.
    – New-To-SQL
    Oct 25 '12 at 13:04
  • Thanks to all for all the help! it's greatly appreciated!
    – New-To-SQL
    Oct 25 '12 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.