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 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?
6条答案
按热度按时间cclgggtu1#
Instead of a grouped
COUNT
you can use it as a windowed aggregate to access the other columnssbdsn5lh2#
Agree with above answer. But If you don't want to use Windows functions which might not work properly on all DBs you can join to itself on city and full name after the group by and having and then get the addresses
sdnqo3pr3#
Try the Following Code:
uelo1irk4#
If you have a unique id or the address is always different, you can try:
Although I would probably go with the window function approach, you might find that under some circumstances, this is faster (especially if you have an index on
employee(fullname, city, address)
).9njqaruj5#
Here you go with the solution:
Result :
xe55xuns6#
This will give you all yours answer