How do I remove duplicate entries in SQL query? (I can implement in PHP too)

Question

So I'm writing a PHP script that takes out the duplicate entries of this SQL query:

SQL:

SELECT DISTINCT a.pname, a.pnumber, a.plocation, 
    CASE WHEN a.plocation = b.dlocation then 'Local'
         ELSE 'Remote' 
    END AS ptype
FROM PROJECT a LEFT JOIN DEPT_LOCATIONS b on a.dnum = b.dnumber
order by a.pnumber;

Output:

    PNAME              PNUMBER PLOCATION       PTYPE
    --------------- ---------- --------------- ------
    ProductX                 1 Bellaire        Local
    ProductX                 1 Bellaire        Remote
    ProductY                 2 Sugarland       Local
    ProductY                 2 Sugarland       Remote
    ProductZ                 3 Houston         Local
    ProductZ                 3 Houston         Remote
    Computerization         10 Stafford        Local
    Reorganization          20 Houston         Local
    Newbenefits             30 Bellaire        Remote
    Virtualization          40 Houston         Local
    Virtualization          40 Houston         Remote
    CRM                     50 Stafford        Local
    Bladeservers            60 Houston         Local

The problem is sometime the results show both Local and Remote. I want it so that if there is a Local, the Remote wouldn't show. Is that a possibility in SQL or do I have to do that in PHP?

Either dnumber is not unique, two part numbers might have the same dnumber or one part number may appear with different dnumbers on different rows, so which one is it? — ffflabs
– ffflabs, Commented Nov 25, 2014 at 1:42

O. Jones · Accepted Answer · 2014-11-25 02:24:50Z

2

I think you are saying that you want to see the Local row if it is there, otherwise you want to see the Remote row. If that is right, you can do this, taking advantage of the fact that Local's value is less than Remote's. We'll use an aggregate query with MIN() to do this.

SELECT pname, pnumber, plocation, MIN(ptype) AS ptype
  FROM (
        SELECT a.pname, a.pnumber, a.plocation, 
               CASE WHEN a.plocation = b.dlocation then 'Local'
                                                   ELSE 'Remote' 
                END AS ptype
          FROM PROJECT a 
          LEFT JOIN DEPT_LOCATIONS b on a.dnum = b.dnumber
        ) AS q
 GROUP BY pname, pnumber, plocation
 ORDER BY pnumber

answered Nov 25, 2014 at 2:24

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

radar Over a year ago

good thinking, i was thinking of doing with union and not exists clause.

Sohaib Akhter · Accepted Answer · 2014-11-25 13:11:11Z

0

This is the statement that really solved my problem:

 WITH CTE AS 
 (SELECT x.PNAME, x.PNUMBER, x.PLOCATION, PTYPE, ROW_NUMBER() OVER (Partition by PNAME order by PTYPE) R
 FROM (SELECT prj.pname, prj.pnumber, prj.plocation, CASE WHEN plocation = dlocation then 'Local' ELSE 'Remote' END AS 
 ptype FROM Project prj JOIN DEPT_LOCATIONS dept on prj.dnum = dept.dnumber) x )
 SELECT PNAME, PNUMBER, PLOCATION, PTYPE
 FROM CTE
 WHERE R = 1
 ORDER BY PNUMBER;

answered Nov 25, 2014 at 13:11

Sohaib Akhter

455 bronze badges

Collectives™ on Stack Overflow

How do I remove duplicate entries in SQL query? (I can implement in PHP too)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related