PDA

View Full Version : column value occurs in more than 1 row



tom123
Oct 31st, 2006, 09:11 AM
Hey guys,

I need to display all records in a table where the same id value occurs in more than one record (in same colum). Could anyone help out? this oracle stuff if kinda new to me.

Thanks

Roelf
Nov 2nd, 2006, 08:24 AM
ok, to check which id's occur more than once, do this query:

SELECT id
FROM table
GROUP BY id
HAVING (COUNT(id) > 1))

now you want to return the records which have one of these id's, so enter this query as a subquery in your where statement like:


SELECT *
FROM table
WHERE id IN
(
SELECT id
FROM table
GROUP BY id
HAVING (COUNT(id) > 1))
)

tom123
Nov 2nd, 2006, 09:21 AM
Brillant Roelf, thanks a million :thumbsup: