• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 73
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 2
  • 2
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now