Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Help to remove duplicate records

Posted on 2014-04-19
7
Medium Priority
?
401 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 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