Oracle - query to return diffrences

Hi,

I have this table where 3 columns could have same information where the last 4 columns can sometime be the same and other times can have different values.

In below sample that you will also have in the attachment, you see that Brampton John Carson have 3 records but when you look at the columns Result1 to Result4, 2 rows are the same versus the other is different.

As for Ajax Bruce Saxton, Result1 to Result4 have the same values on both rows.

The objective for me is to only extract from the table when i have the same value from City, Name and Family name and when i have more then 1 difference in columns Reult1 to Result4.

Example. If my table got:
Table1_sample
The query result would return:
Query return
How can i do that?

Thank you for your help
Table1.xlsx
LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented: 
SELECT city, name, family_name, result1, result2, result3, result4
  FROM (SELECT t.*,
               COUNT(DISTINCT result4) OVER (PARTITION BY city, name, family_name) cnt,
               ROW_NUMBER() OVER(PARTITION BY city,name,family_name,result4 ORDER BY ROWNUM) rn
          FROM yourtable t)
 WHERE cnt != 1
   AND rn = 1

Open in new window

0
Wilder1626Author Commented:
Hi sdstuber,

is it only looking at result4 diffrences or all of the Results 1,2,3 and 4?
0
sdstuberCommented:
it's only comparing result 4

were you looking for any difference in any of those columns? if so sorry, I misunderstood the requirement.

To check across all 4 columns you'd use a similar idea though

SELECT city,
       name,
       family_name,
       result1,
       result2,
       result3,
       result4
  FROM (SELECT city,
               name,
               family_name,
               result1,
               result2,
               result3,
               result4,
               COUNT(*) OVER (PARTITION BY city, name, family_name) cnt
          FROM (SELECT t.*,
                       ROW_NUMBER()
                           OVER(PARTITION BY city,
                                             name,
                                             family_name,
                                             result1,
                                             result2,
                                             result3,
                                             result4
                                ORDER BY ROWNUM)
                           rn
                  FROM yourtable t)
         WHERE rn = 1)
 WHERE cnt > 1

Open in new window


Another option, sum the counts, they should be 4 if all the same.  I prefer the one above though.


SELECT city,
       name,
       family_name,
       result1,
       result2,
       result3,
       result4
  FROM (SELECT t.*,
                 COUNT(DISTINCT result1) OVER (PARTITION BY city, name, family_name)
               + COUNT(DISTINCT result2) OVER (PARTITION BY city, name, family_name)
               + COUNT(DISTINCT result3) OVER (PARTITION BY city, name, family_name)
               + COUNT(DISTINCT result4) OVER (PARTITION BY city, name, family_name)
                   cnt,
               ROW_NUMBER()
                   OVER(PARTITION BY city,
                                     name,
                                     family_name,
                                     result1,
                                     result2,
                                     result3,
                                     result4
                        ORDER BY ROWNUM)
                   rn
          FROM yourtable t)
 WHERE cnt != 4 AND rn = 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Or, have the query look at the previous and next values for the row and only pick the differences.

Probably more elaborate and less efficient than what sdstuber posted, but may be a little easier to understand.
SELECT city, 
       name, 
       family_name, 
       result_1, 
       result_2, 
       result_3, 
       result_4 
FROM   (SELECT city, 
               name, 
               family_name, 
               result_1, 
               result_2, 
               result_3, 
               result_4, 
               Lead(result_1) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) 
               lead_result_1, 
               Lead(result_2) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) 
               lead_result_2, 
               Lead(result_3) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) 
               lead_result_3, 
               Lead(result_4) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) 
               lead_result_4, 
               Lag(result_1) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) lag_result_1 
               , 
               Lag(result_2) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) lag_result_2 
               , 
               Lag(result_3) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) lag_result_3 
               , 
               Lag(result_4) 
                 over ( 
                   PARTITION BY city, name, family_name 
                   ORDER BY result_1, result_2, result_3, result_4) lag_result_4 
        FROM   mytab) 
WHERE  result_1 != Nvl(lead_result_1, lag_result_1) 
        OR result_2 != Nvl(lead_result_2, lag_result_2) 
        OR result_3 != Nvl(lead_result_3, lag_result_3) 
        OR result_4 != Nvl(lead_result_4, lag_result_4);

Open in new window

0
Wilder1626Author Commented:
That's for your help. this is exactly what i was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.