I have a problem that might actually be quite trivial; however, I do not find a solution to it. Here it is:
You have the following table TAB
ID | Name |...
1 | Mouse |
2 | Duck |
3 | Mouse |
4 | Goose |
I want to select all rows where the name is the identical; i.e. in this example, I want to select row 1 and 3. Does anyone know the correct syntax for this statement? (I tried to solve it by using a view, but that wasn't the right track....)
-Thanks a lot!
I don't know if this helps but one command you might want to think about is HAVING. So if you do
GROUP BY name
HAVING COUNT(*) > 1
This would display all names where there is more than one record in the table with the same name.
select * from TAB
where name in(select name from TAB group by name having count(1) > 1)
that's exactly what I've been looking for! Thanks a lot for the hint!
Select * from table_name
where name in (select name
group by name
having count(name) >= 2)
[Edited by PSoni on 06-12-2001 at 08:26 AM]
Click Here to Expand Forum to Full Width