Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

use sql to find same record

Hi,

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?
0
kevin_yee_teck_ping
Asked:
kevin_yee_teck_ping
1 Solution
 
andrewstCommented:
select tel_no, count(*)
from table
group by tel_no
having count(*) > 1;
0
 
riazpkCommented:
select * from
(
select name,tel_no,address,sex, row_number() over (partition by tel_no) rno
from table
)
where rno<>1
0
 
oratimCommented:
select * from table where tel_no in (select tel_no from table having count(tel_no) > 1 group by tel_no)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now