How to export SQL records without duplicates?

Asked

Viewed 1,405 times

5

I’m trying to remove some duplicate records from a table and I searched the Internet for how to do that, and I found something about distinct.

My scenario is:

I have a table that has a record with all duplicate columns.

ID | Nome   | Idade
1  | Teste  | 20
1  | Teste  | 20
1  | Teste  | 20
2  | Teste2 | 28

Now I’m trying to export to a temporary table with distinct, but when I export only with ID in my query the right query and export without duplicates:

SELECT DISTINCT t.ID
INTO Temp
FROM tabela t

however if I do :

SELECT DISTINCT t.*
INTO Temp
FROM tabela t

It exports everything down to duplicates.

How can I export all records with distinct or without him?

sql sql-server
  • 1

    Welcome to Stack overflow. The way your question is it provides little detail to help you. Try to inform in detail what you have already tried. Start by doing a [tour] and read the guide [Ask].

  • Ah, please insert also via the link [Edit] which database you are using.

  • The bank is the sql gmsantos is there in the title. Sorry for my lack of experience here on the site I am even beginner.

  • Lucas, no problem, with time you get the hang of it. You refer to Microsoft SQL Server?

  • Yes. I even put an example there of how the table is.

  • you tried to use group by?

  • I have never used "nickname. *" with DISTINCT. Enter the name of all fields - SELECT DISTINCT ID, Nome, Idade INTO Temp FROM tabela that should work.

Show 2 more comments

3 answers

1

I am to "steal" an answer I found in the OS in English ;)

You should group the records. I think we can ignore the repeated Ids (let me know if that’s not convenient). So we will use all fields except the ID field. Something like:

SELECT MIN(ID) as ID, Nome, Idade 
FROM Temp
GROUP BY Nome, Idade

Note that you can use MAX instead of MIN... What matters is getting a single ID for each group of duplicate records.

In practice it happens that each information distinct from the system will be obtained. And for each group of repeated information, only the smallest ID (or larger if you use MAX instead of MIN) will be obtained.

When you have these results, you will have two alternatives to fulfill your goal:

  • You can export the result of this query. You will only have different data in the export result;

  • The least recommended way is to delete all repeated records from the table. This takes courage as any error can erase data beyond the ones you want to delete. I recommend having a backup if you want to go this way.

The command is something like this:

DELETE * 
FROM Temp
LEFT OUTER JOIN (
    SELECT MIN(ID) as ID, Nome, Idade
    FROM Temp
GROUP BY Nome, Idade
) as RegistosAManter ON
Temp.ID = RegistosAManter.ID
WHERE
   RegistosAManter.ID IS NULL

The query from FROM retrieves all records from the table. The ID’s of each record appear twice (because we are using a JOIN), but on the right side of the result the ID’s of the repeated elements will be null. The delete command will remove these records from the table.

  • The ID field is not always varying, according to the question.

  • @Caffé was worth! I will edit here :)

  • 1

    If you use GROUP BY, SELECT ID, Nome, Idade FROM Temp GROUP BY ID, Nome, Idade solves the problem. No need for match function.

1

You can use the command INTERSECT it removes duplicated lines from the final query.

SELECT *
FROM   SuaTabela
WHERE  ColunaDesejada BETWEEN 1 AND 100

INTERSECT

SELECT *
FROM   SuaTabela
WHERE  ColunaDesejada BETWEEN 50 AND 200;

I hope it helps you.

0

First I believe that your modeling "even if for testing" there must be a primary key, which nay should allow the column "ID" get duplicity, but OK, let’s go for something like testing.

in this case, I believe that only solve using group by.

create table t (ID int, Nome varchar(20), Idade int)
go

insert into t values 
(1, 'Teste', 20),
(1, 'Teste', 20),
(1, 'Teste', 20),
(2, 'Teste', 28)
go

select t.id, t.nome, t.idade , count(1)
from t
group by t.id, t.nome, t.idade

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.