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).
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:
The query result would return:
How can i do that?
Thank you for your help
Table1.xlsx
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:
The query result would return:
How can i do that?
Thank you for your help
Table1.xlsx
Asked:
2-
2
2 Solutions
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
Another option, sum the counts, they should be 4 if all the same. I prefer the one above though.
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
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;
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.
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);
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 Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window