Find duplicate records based on two columns

Question

I have a table named employee, there are many records in this table. Here is some sample data :

fullname  | address  |  city
-----------------------------
AA          address1    City1
AA          address3    City1
AA          address8    City2
BB          address5    City2
BB          address2    City1
CC          address6    City1
CC          address7    City2
DD          address4    City1

I want to have a SELECT query in sql server which will show only the duplicate records based on the columns fullname and city. For the given data and considering the condition, only the first two records is duplicate. So my expected output should be like below :

fullname  | address  |  city
-----------------------------
AA          address1    City1
AA          address3    City1

To get this output, i have written this query :

select fullname, city from employee group by fullname, city having count(*)>1

As you can see, it selects two columns only and thus it is giving the following output :

fullname  | city
------------------
AA          City1

If i re-write the query like below :

select fullname, city, address from employee group by fullname, city, address 
having count(*)>1

Unfortunately it is showing no records! Can anybody please help me to write the correct query to get the expected result?

Answer 1

Instead of a grouped COUNT you can use it as a windowed aggregate to access the other columns

SELECT fullname,
       address,
       city
FROM   (SELECT *,
               COUNT(*) OVER (PARTITION BY fullname, city) AS cnt
        FROM   employee) e
WHERE  cnt > 1