?
Solved

SQL Query Help to remove duplicate records

Posted on 2014-04-19
7
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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