Select one name per person where spelling variations exist

Question

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

Answer 1

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) 
SELECT Emp_ID, MIN(Name) 
FROM SourceTable 
GROUP BY Emp_ID ;

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 
ORDER BY Name ;