Q

Employees with the same first and last names in SQL

How do I SELECT a list of employees who have the same last and first name in SQL?

How do I SELECT a list of employees who have the same last and first name? The primary key of the EMP table is EmpID. The returned list must include the EmpID, Lastname, Firstname and DeptID sorted by EmpID, Lastname, Firstname and DeptID.

This looks suspiciously like a homework question, but perhaps it isn't.

Let's start with the ORDER BY clause. The first column, EmpID, is the primary key. Therefore all other ORDER BY columns are superfluous. If this is homework, the required sort order was a trick question. Besides, ORDER BY EmpID would scramble all the dupes.

Now, to tackle the SQL. Employees with the same first and last names will necessarily be found on separate rows, and whenever more than one row is required to satisfy a criterion, we should immediately think of grouping.

The following query identifies only those first and last name combinations belonging to more than one employee. Note the GROUP BY and HAVING clauses.

select Lastname
     , Firstname
  from Employees
group
    by Lastname
     , Firstname
having count(*) > 1 

In order to show the details of these employees, we simply use this query as a derived table in the details query:

select EmpID
     , Lastname
     , Firstname
     , DeptID
  from Employees
 where ( Lastname 
       , Firstname ) 
    in ( select Lastname
              , Firstname
           from Employees
         group
             by Lastname
              , Firstname
         having count(*) > 1 
       )
order
    by Lastname
     , Firstname
     , DeptID

This ORDER BY is somewhat more useful, eh.

P.S. If this wasn't a homework assignment, the above syntax will probably not work in your particular database. Use the derived table in an INNER JOIN, joining on Lastname and Firstname columns.

This was first published in May 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close