Remove Duplicates records from view

Question

I have a view. How can I display only distinct records from it. There are 19 columns. I want to display all in single without any duplicate, and I don't want to delete the duplicates. I require it only for viewing purpose .

        SELECT DISTINCT([Complaint Number]),[Complaint_For],[Total_Complaint_Qty]
        ,[Defects],[Customer Code ],[Location],[CutomerName],[KUNUM],[QMNUM]
        ,[Responsible_KAM] 
        FROM [CCCMPREPOSITORY].[dbo].[VW_Final_]

If I use the previous query it returns 1000 Records. But if I use the following query it returns exact record without duplicate . But I want all columns to be displayed.

        SELECT DISTINCT([Complaint Number])
        FROM [CCCMPREPOSITORY].[dbo].[VW_Final_]

Answer 1

You must have duplicate records for some/all Complaint Numbers, so using DISTINCT will not work. Consider a simple table such as

Column1    |    Column2
-----------+------------
A          |    X
A          |    Y

If you want just one record for Column1 = A, SQL has no way of knowing whether to put X or Y in Column2. This is the same problem you are having but with 19 columns, not 2. You need to implement some kind of logic as to how to decide which row will show for each Complaint Number. So for the above table if I wanted X to show in Column2 I would use the following query:

SELECT  Column1,
        Column2
FROM    (   SELECT  Column1,
                    Column2,
                    ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column2 ASC) [RowNumber]
            FROM    MyTable
        ) t
WHERE   RowNumber = 1

Here I am using the ROW_NUMBER() function to prioritise each row, then only displaying the one with the highest priority. If I didn't care what order they came out in I might use something like this to pick a random row.

ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY NEWID()) [RowNumber]

Since I don't know what logic to apply to your query I cannot post exactly what you need, but I can try and get you started:

SELECT  [Complaint Number],
        [Complaint_For],
        [Total_Complaint_Qty],
        [Defects],
        [Customer Code ],
        [Location],
        [CutomerName],
        [KUNUM],
        [QMNUM],
        [Responsible_KAM]
FROM    (   SELECT  [Complaint Number],
                    [Complaint_For],
                    [Total_Complaint_Qty],
                    [Defects],
                    [Customer Code ],
                    [Location],
                    [CutomerName],
                    [KUNUM],
                    [QMNUM],
                    [Responsible_KAM],
                    ROW_NUMBER() OVER(PARTITION BY [Complaint Number] ORDER BY Complaint_For, Defects) AS RowNumber
            FROM    [CCCMPREPOSITORY].[dbo].[VW_Final_]
        ) data
WHERE   RowNumber = 1

You would just need to fiddle around with the ORDER BY within the ROW_NUMBER function to fit your needs.

Answer 2

Try:

SELECT [Complaint Number], MAX([Complaint_For]), MAX([Total_Complaint_Qty]), ...
FROM [CCCMPREPOSITORY].[dbo].[VW_Final_]
GROUP BY Complaint Number