PDA

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



tom123
10-31-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
11-02-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
11-02-2006, 09:21 AM
Brillant Roelf, thanks a million :thumbsup: