Solved

SQL Query Help to remove duplicate records

Posted on 2014-04-19
7
392 Views
Last Modified: 2014-04-20
Hello there,
 
I have this query which returns some cols from tables some of the cols are not duplicate but I want to get distinct records based on one of the cols(dbo.supplierOrder.ID  ). how can I do it. below is my query. I also attached shot of the result.

SELECT
        dbo.supplierOrder.ID  as SoID,
        dbo.supplierOrder.sectionID,
        dbo.supplierOrder.branchID,
        dbo.supplierOrder.supplierID,
        dbo.Supplier.engName as supplierengName,
        dbo.Supplier.perName as supplierperName,
        dbo.supplierOrder.supplierOrderNo,
        dbo.supplierOrder.supplierOrderCreated,
        dbo.supplierOrder.supplierOrderConfirmStatus,
        dbo.supplierOrder.orderCompleteStatus,
        dbo.supplierOrder.confirmDate,
        dbo.supplierOrderDetails.id  as SodID,
        dbo.supplierOrderDetails.productID,
        dbo.supplierOrderDetails.orderQty,
        dbo.supplierOrderDetails.isApprove,
        dbo.supplierOrderDetails.isComplete  
    FROM
        dbo.supplierOrderDetails  
    INNER JOIN
        dbo.supplierOrder  
            ON      (
                dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID
            )  
    INNER JOIN
        dbo.Supplier  
            ON      (
                dbo.supplierOrder.supplierID = dbo.Supplier.id
            )  
    WHERE
        dbo.supplierOrder.supplierOrderConfirmStatus = 1  
        AND dbo.supplierOrder.orderCompleteStatus = 0  
        AND dbo.supplierOrderDetails.isComplete = 1 ;   

Open in new window

0
Comment
Question by:zolf
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40011052
You forgot the intended image, but I'm not sure that will be enough

Chances are you need to use a "group by" clause,
 or you need to use row_number() over() ,
so that you may select, for example, the most recent record.

see "Select Distinct is returning duplicates ..."
and DISTINCT and GROUP BY... and why does it not work for my query?
0
 

Author Comment

by:zolf
ID: 40011053
sorry about that. I have attached the shot now..

11
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40011061
ok, but what about..."distinct" works across the whole row, if some part of a row is different to other rows, then it is distinct

If you want something other than that, then "select distinct" is not the technique to help. At this point I don't know what you want as the result.

Perhaps you could provide the "expected result"?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:zolf
ID: 40011067
PortletPaul

thanks a lot for pointing that out. I understood my mistake and need to re-design my code.cheers!!
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 40011073
group by SoID ?
0
 

Author Comment

by:zolf
ID: 40011076
by: skullnobrains

thanks for your comments but that wont work I get this error ---- is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 40011101
you need to apply an aggregate function to each of the fields in the select list (except SoID)

for example

SELECT
        dbo.supplierOrder.ID  as SoID,
        max(dbo.supplierOrder.sectionID),
...
group by SoID

then i have no idea which aggregate function would be needed to fit your needs
min, max, sum, count, avg should be available and perhaps a few others

--

if you want a complete specific row rather than aggregates, you can use something like

select
ROW_NUMBER() over (PARTITION BY SoID order by SupplierOrderCreated) as num
...
where num = 1

with your existing query and no group by clause
the above would select the row containing the oldest order for each SoID
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question