CODEWITHSUNDEEP
sqloracle-database
user1895076
user1895076

Reputation: 749

Oracle SQL - Comparing Rows

I have a problem I'm working on with Oracle SQL that goes something like this.

TABLE 

 PurchaseID    CustID      Location  
----1------------1-----------A  
----2------------1-----------A    
----3------------2-----------A  
----4------------2-----------B  
----5------------2-----------A  
----6------------3-----------B  
----7------------3-----------B

I'm interested in querying the Table to return all instances where the same customer makes a purchase in different locations. So, for the table above, I would want:

OUTPUT 

PurchaseID    CustID      Location  
----3------------2-----------A  
----4------------2-----------B  
----5------------2-----------A

Any ideas on how to accomplish this? I haven't been able to think of how to do it, and most of my ideas seem like they would be pretty clunky. The database I'm using has 1MM+ records, so I don't want it to run too slowly.

Any help would be appreciated. Thanks!

Upvotes: 1

Views: 9389

Answers (6)

user3553260
user3553260

Reputation: 721

This makes most sense to me as I was trying to return the rows with the same values throughout the table, specifically for two columns as shown in this stackoverflow answer here.

The answer to your problem in this format is:

SELECT DISTINCT a.*
FROM TEST a
INNER JOIN TEST b
ON a.CUSTOMERID = b.CUSTOMERID AND
a.LOCATION <> b.LOCATION;

However, the solution to a problem such as mine with two columns having matching values in multiple rows (2 in this instance, would yield no results because all PurchaseID's are unique):

SELECT DISTINCT a.*
FROM TEST a
INNER JOIN TEST b
ON a.CUSTOMERID = b.CUSTOMERID AND
a.PURCHASEID = b.PURCHASEID AND
a.LOCATION <> b.LOCATION;

Although, this wouldn't return the correct results based on the what needs to be queried, it shows that the query logic works

SELECT DISTINCT a.*
FROM TEST a
INNER JOIN TEST b
ON a.CUSTOMERID = b.CUSTOMERID AND
a.PURCHASEID <> b.PURCHASEID AND
a.LOCATION = b.LOCATION;

If anyone wants to try in Oracle here is the table and values to insert:

CREATE TABLE TEST (
PurchaseID integer,
CustomerID integer,
Location varchar(1));

INSERT ALL
  INTO TEST VALUES (1, 1, 'A')
  INTO TEST VALUES (2, 1, 'A')
  INTO TEST VALUES (3, 2, 'A')
  INTO TEST VALUES (4, 2, 'B')
  INTO TEST VALUES (5, 2, 'A')
  INTO TEST VALUES (6, 3, 'B')
  INTO TEST VALUES (7, 3, 'B')
SELECT * FROM DUAL;

Upvotes: 0

erbsock
erbsock

Reputation: 1217

This should only require one full table scan.

create table test (PurchaseID number, CustID number, Location varchar2(1));
insert into test values (1,1,'A');
insert into test values (2,1,'A');
insert into test values (3,2,'A');
insert into test values (4,2,'B');
insert into test values (5,2,'A');
insert into test values (6,3,'B');
insert into test values (7,3,'A');

with repeatCustDiffLocations as (
    select PurchaseID, custid, location, dense_rank () over (partition by custid order by location) r
    from test)
select b.*
from repeatCustDiffLocations a, repeatCustDiffLocations b
where a.r > 1
and a.custid = b.custid;

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

In English:

Select a row if another row exists with the same customer and a different location.

In SQL:

SELECT *
FROM atable t
WHERE EXISTS (
  SELECT *
  FROM atable
  WHERE CustID = t.CustID
    AND Location <> t.Location
);

Upvotes: 6

Taryn
Taryn

Reputation: 247620

You should be able to use something similar to the following:

select purchaseid, custid, location
from yourtable
where custid in (select custid
                  from yourtable
                  group by custid
                  having count(distinct location) >1);

See SQL Fiddle with Demo.

The subquery in the WHERE clause is returning all custids that have a total number of distinct locations that are greater than 1.

Upvotes: 7

Declan_K
Declan_K

Reputation: 6826

Here's one approach using a sub-query

SELECT T1.PurchaseID
        ,T1.CustID
        ,T1.Location
FROM    YourTable T1
INNER JOIN
        (SELECT T2.CustID
                ,COUNT (DISTINCT T2.Location )
        FROM    YourTable T1
        GROUP BY
                T2.CustID
        HAVING  COUNT (DISTINCT T2.Location )>1
        ) SQ
ON      SQ.CustID = T1.CustID

Upvotes: 0

Lamak
Lamak

Reputation: 70638

SELECT *
FROM YourTable T
WHERE CustId IN (SELECT CustId
                 FROM YourTable
                 GROUP BY CustId
                 HAVING MIN(Location) <> MAX(Location))

Upvotes: 8

Related Questions