T-SQL Query Results Not as Expected Deduplication

Question

Hi Guys,

I am attempting to get all records where and Id field exists more than once, trouble is my query is returning nothing and I have no idea as to why!? And this is the only method I know.

Some more information:

• There are up to 8 of the same Order Numbers
• Each set is grouped by ProcessOrder, I require the lowest value of these but only for Dupes
• The rest of the data can be different or the same it varies.

• The data has three IDs included, all are dupes these are:

  1.Customer Number

  2.Corp Customer Number

  3.Order Number

Here is my code:

        select 
[Customer Number], 
[Corporate Customer Number], 
[Order Date], 
[Order Number],
[Order No], 
[Order Line Status],
[Payment Method] , 
[ProcessOrder], 
[Order Platform] 

from Temp_ICOSOrder

    group by 
[Customer Number], 
[Corporate Customer Number], 
[Order Date], 
[Order Number],
[Order No], 
[Order Line Status],
[Payment Method] ,
[ProcessOrder] , 
[Order Platform]

    having COUNT([Order Number]) > 1

Any help is much appriciated!

Answer 1

Remove [Order Number] from the group by clause.

If you group by it, the count will always be 1!