[SOLVED] In sql I want to a query in which it gives me the duplicates. However, i would like to see the duplicates side by side. Instead of just count

In sql I want to a query in which it gives me the duplicates. However, i would like to see the duplicates side by side. Instead of just count

Select employee.id,count(employee. Id), employee.name
From employee_database 
Group by employee.id, employee.name 
Having count (employee.id) >1

Solution

This will probably help you. As Igor mentioned on points.

Select 
      e1.id,
      e1.name,
      e2.id as DupEmployeeID,
      e2.name as DupEmployeeName
   From 
      employee e1
         JOIN employee e2
            on e1.name = e2.name
           AND e1.id < e2.id
   order by
      e1.name

Sample Data

EmployeeID   Name
1            Bill
2            Mary
3            Paul
4            Bill
5            Joe
6            Mary
7            Sandy
8            Mary

Now, this will work and may need to be adjusted because of the self-join on the common part (name), and the ID of the first table is LESS than the second, will prevent comparisons such as

1 Bill   4 Bill
4 Bill   1 Bill  (this one will NOT be shown as the instance already counted for

But in situations like Mary you would get things like

2 Mary  6 Mary
2 Mary  8 Mary
6 Mary  8 Mary

Now, this might not be as practical, but if there were other information on file like an address, phone, etc, you could pull those respective columns into the query as I sampled with the original and the "DupEmployee" columns.

FEEDBACK.

I Think I got what you are looking for, per the formatted response you commented to me, I get

EmployeeID Name  Count 
1          Bill  2 
4          Bill  2 
2          Mary  3 
6          Mary  3 
8          Mary  3 
5          Joe   1 
3          Paul  1 
7          Sandy 1

Which is a list of all names and how many instances OF that name. The basis of the sort is anyone with more than 1 show up first and in count order. Within that order, list the names alpha order. So, since there were 2 "Bill" names in my sample, those show up first as more than one. Similarly with "Mary" second and each of those entries, and finally all the individual name instances.

To do this, you need to pre-query names and counts, then join back to the original table like

select
      e1.EmployeeID,
      e1.Name,
      NameGrp.NameCnt as count
   from
      employee e1
         join ( select
                      e2.Name,
                      count(*) as NameCnt
                   from
                      Employee e2
                   group by
                      e2.Name ) NameGrp
            on e1.Name = NameGrp.Name
   order by
      case when NameGrp.NameCnt > 1 then 1 else 2 end,
      NameGrp.NameCnt,
      e1.Name