CODEWITHSUNDEEP
phpmysqlsql-server
Sohaib Akhter
Sohaib Akhter

Reputation: 45

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

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?

Upvotes: 0

Views: 76

Answers (2)

Sohaib Akhter
Sohaib Akhter

Reputation: 45

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;

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

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

Upvotes: 2

Related Questions