CODEWITHSUNDEEP
sqlselectdistinct
BBGMetalmeccanica BBG
BBGMetalmeccanica BBG

Reputation: 23

I can't figure out how to do this DISTINCT

Good morning

I tried and tried to understand why this Query gives the usual error on Group By. I would like to find the duplicate lines and delete them. I found this query on Microsoft's MSDN but despite this it keeps giving me this error on Group By. The main table has 3 fields "Id, Item, Description", the table name is "tlbDescription", this query should in theory create a table named "duplicate_table" insert the duplicate values inside the "duplicate_table", then delete the values from table "tlbDescription" and finally delete the table "duplicate_table". If someone can kindly give me a hand Thank you Fabrizio This is the query:

SELECT DISTINCT *
INTO duplicate_table
FROM [tlbDescrizione]
GROUP BY [Articolo]
HAVING COUNT([Articolo]) > 1

DELETE [tlbDescrizione]
WHERE [Articolo]  IN (SELECT [Articolo] FROM duplicate_table)

INSERT [tlbDescrizione] 
SELECT * FROM duplicate_table

DROP TABLE duplicate_table

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This query doesn't make sense:

SELECT DISTINCT *
INTO duplicate_table
FROM [tlbDescrizione]
GROUP BY [Articolo]
HAVING COUNT([Articolo]) > 1;

It is selecting all columns but is an aggregation query because of the GROUP BY. Hence, the SELECT columns are inconsistent with the GROUP BY columns and you get an error.

If you want all the columns then you can use window functions:

SELECT DISTINCT *
INTO duplicate_table
FROM (SELECT d.*, COUNT(*) OVER (PARTITION BY d.Articolo) as cnt
      FROM tlbDescrizione d
     ) d
WHERE cnt > 1;

Or, if you want only the ids:

SELECT Articolo
INTO duplicate_table
FROM tlbDescrizione
GROUP BY [Articolo]
HAVING COUNT(*) > 1;

Upvotes: 1

Related Questions