Start Free Trial

asked on 3/3/2018

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:

User generated image

The query result would return:

User generated image

How can i do that?

Thank you for your help
Table1.xlsx

Oracle Database SQL

Last Comment

3/4/2018

3/3/2018

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

3/3/2018

ASKER

Hi sdstuber,

is it only looking at result4 diffrences or all of the Results 1,2,3 and 4?

ASKER CERTIFIED SOLUTION

3/3/2018

Blurred text

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

3/3/2018

Blurred text

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

3/4/2018

ASKER

That's for your help. this is exactly what i was looking for.

Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY