Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query Help to remove duplicate records

Posted on 2014-04-19
7
Medium Priority
?
404 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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

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 27

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 27

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 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