[SOLVED] Selecting duplicate values

[tommo74]

I have a table called AADETAILS with two colums ('Name' and 'ID').

The ID column is not supposed to have any duplicates, so I need a query that returns a list of all the duplicate ID's (with their Name). For example, if there is an ID with a value of 123 duplicated three times and an ID with a value of 456 duplicated four times, I need a list of the Name and ID of the three instances of 123 and the four instances of 456.

I have the following :

SELECT Name, ID
FROM AADETAILS
GROUP BY Name, ID
HAVING COUNT(ID) > 1

its not returning anything, but it should (I can see the duplicates in the table if I view all the data in it), whats wrong with that query ?

Thanks

 

[pikemsu28]

drop the group by name and it should work

 

SELECT Name, ID
FROM AADETAILS
GROUP BY ID
HAVING COUNT(ID) > 1

[Barand]

That will give the duplicated ids but only one of the names.

 

Try

SELECT id , GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as names
FROM aadetails
GROUP BY id
HAVING COUNT(*) > 1

[tommo74]

Thanks, when I try and run the query it gives the following error:

Error starting at line 1 in command:
SELECT id , GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as names
FROM aadetails
GROUP BY id
HAVING COUNT(*) > 1
Error at Command Line:1 Column:30
Error report:
SQL Error: ORA-00907: missing right parenthesis

 

I've tried playing around with the parenthesis but nothing doing.

 

[bubblegum.anarchy]

Confirm that Oracle uses the same GROUP_CONCAT function as MySQL does.

 

EDIT: Oracle does not appear have a group_concat function.

[tommo74]

Yes, it appears that you are right about that. Sorry I should have mentioned I'm using Oracle.

[tommo74]

I ended up using this which works fine:

 

select name, id from aadetails 
where id in(select id from aadetails group by id having count(*)>1)
order  by name;