Remove repeated lines in all aspects on Oracle

Question

In a column REGISTER I have two lines repeated in all aspects:

NOME   ID    EMAIL           SEXO    
JOAO   1234  [email protected]  M
JOAO   1234  [email protected]  M

How to compare these two rows, check if all columns are identical and delete one of them in order to result:

NOME   ID    EMAIL           SEXO    
JOAO   1234  [email protected]  M

Answer 1

Understanding that you want to delete duplicate records from the database but, keeping one (1) of them, I suggest using the rowid.

The ROWID is a pseudo-column that returns the address of a record, structurally you won’t see it, but, it’s there.
It uniquely identifies a row within a table.

In the script below we have:

• A sub-query seeking all minors rowid of each repeated grouping;

• A command delete in all records that are not in the minimum grouping made in the sub-query;

Script:

delete from cadastro
where rowid not in
     (select min(rowid)
      from cadastro
group by nome, id, email, sexo);

With this, duplicates will be eliminated leaving at least one of each.

Note: You should never store one rowid in its tables as a key value (unique).

Answer 2

In sql server, the method below works. In oracle I think it is similar:

select *
into #table
from(   select nome = 'JOAO', id=1234, email='[email protected]', sexo='m'
        union all
        select nome = 'JOAO', id=1234, email='[email protected]', sexo='m'
        union all
        select nome = 'manuel', id=1237, email='[email protected]', sexo='m') t

   ;WITH cte AS (
        SELECT  *, 
                row_number() OVER(PARTITION BY nome,id,email,sexo ORDER BY nome,id,email,sexo) AS rn
        FROM #table
    )
    DELETE cte WHERE rn > 1

select * 
from #table

drop table #table

Answer 3

Use a distinct.

Select Distinct * From Tabela;

See working on SQL Fiddle.