use sql to find same record


I have a table contain 1000 record, I would like to search whether got any record got the same data, for example I would like to check 'tel_no' field, see whether the record contain 2 or more same data inside, anybody knows how to write an sql statement to get it out?

for example

Name        tel_no       address      sex
a               5555        abc             m
b               5656        bbb             f
c               5555         dfdf             m

how to write a sql statement to see find out the field 'tel_no' actually contain same data '5555' twice here?
Who is Participating?
andrewstConnect With a Mentor Commented:
select tel_no, count(*)
from table
group by tel_no
having count(*) > 1;
select * from
select name,tel_no,address,sex, row_number() over (partition by tel_no) rno
from table
where rno<>1
select * from table where tel_no in (select tel_no from table having count(tel_no) > 1 group by tel_no)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.