Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I use DISTINCT in the OUTPUT clause?

I'm trying something like:

INSERT INTO MyTable (
       Col1
      ,Col2 )
OUTPUT DISTINCT -- issue is with DISTINCT
       INSERTED.Col1
      ,@otherParameter
       INTO IdListTable
SELECT ColA
      ,ColB
      ,SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

Except SQL doesn't want me to use DISTINCT in the OUTPUT clause. The workaround I thought of was to create a temp table for the output, then INSERT DISTINCT into the IdListTable. Any ideas on a different workaround?

like image 948
Brad Avatar asked May 17 '11 17:05

Brad


People also ask

Can distinct be used in where clause?

By using the WHERE clause with a DISTINCT clause in MySQL queries, we are putting a condition on the basis of which MySQL returns the unique rows of the result set.

What will be the output of SELECT distinct?

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Why we should not use distinct in SQL?

This is why I get nervous about use of " distinct " - the spraddr table may include additional columns which you should use to filter out data, and " distinct " may be hiding that. Also, you may be generating a massive result set which needs to be filtered by the "distinct" clause, which can cause performance issues.

Can we use distinct * in SQL?

Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query.


1 Answers

Replace IdListTable with a temporary table (or a table variable depending upon the number of rows) in the Output statement. Then run a second Insert statement into IdListTable from the temporary table with a Select Distinct.

INSERT INTO MyTable (
       Col1,
       Col2 )
OUTPUT 
       INSERTED.Col1,
       @otherParameter
       INTO #tempIdListTable
SELECT ColA,
       ColB,
       SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

Insert into IdListTable
Select distinct col1, col2 from #tempIdListTable
like image 171
Jeff Siver Avatar answered Nov 13 '22 02:11

Jeff Siver