We help IT Professionals succeed at work.

find duplicate values

saloj
saloj used Ask the Experts™
on
i have following table structure and i would like to display all duplicate it has
-------------------------------------
id account nickname password
-------------------------------------
what could be the query

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Máté FarkasDatabase Developer and Administrator

Commented:
If the id field also can be duplicated
SELECT id, account, nickname, password, COUNT(*) [Count] FROM YourTable
GROUP BY id, account, nickname, password
HAVING COUNT(*) > 1

Open in new window

Author

Commented:
sorry, it has more columns, in that case how do i display all duplicates from column
Máté FarkasDatabase Developer and Administrator

Commented:
Everyy column which you think it can contain duplicate value must be included after the SELECT and after the GROUP BY column list. So you can continue the column list after the password column.

Author

Commented:
but it has date column which is not duplicate but would like to display on result
how to do that
Kamal KhaleefaInformation Security Specialist

Commented:
you can find duplicates value by this query

select count(Coloum name),coloum name from tablename
group by   ColoumName having count(Coloum name)>1  
Database Developer and Administrator
Commented:
If there is a column which have to be seen but does not containt duplicates you can use this

SELECT T1.* FROM YourTable T1 INNER JOIN (
SELECT id, account, nickname, password, COUNT(*) [Count] FROM YourTable  
GROUP BY id, account, nickname, password  
HAVING COUNT(*) > 1
) T2 ON T1.id = T2.id AND T1.account = T2.account AND T1.nickname = T2.nickname AND T1.password = T2.password
...etc

Open in new window