?
Solved

Select unique records only.

Posted on 2014-01-31
3
Medium Priority
?
578 Views
Last Modified: 2014-02-01
create table master_table (
id varchar2(20),
last_name varchar2(20),
first_name varchar2(20));

create table baseball_players (
last_name varchar2(30),
first_name varchar2(30));

insert into master_table (id, last_name, first_name) values ('abc','Jackson', 'Bo');
insert into master_table (id, last_name, first_name) values ('def','Carter', 'Joe');
insert into master_table (id, last_name, first_name) values ('ghi','Carter', 'Joe');

insert into baseball_players (last_name, first_name) values ('Jackson', 'Bo');
insert into baseball_players (last_name, first_name) values ('Carter', 'Joe');
commit;

--the result I want is to eliminate any duplicates
--so skip over Joe Carter completely and just take
--Bo Jackson ie: 1 row.
--i need to keep using the inner join and I need the id column.

select m.id,  m.last_name, m.first_name
from master_table m
inner join baseball_players hp on (lower(trim(m.last_name)) = lower(trim(hp.last_name))
and lower(trim(m.first_name)) = lower(trim(hp.first_name)))
order by m.last_name;  

--i have been trying this below to show the next row and
--if it is the same as the one previous on last_name and first_name then ignore
--else select it. Is this the right path to obtain the result?
--it does not seem to work, can you help finish it so that it does work?
--what I want is to be able to run it again later (with a tweak) to have it select only rows
--that have 1 dup, then 2, then 3 etc...but first it must not select any with dups.

select m.id,  trim(m.last_name), trim(m.first_name),
LEAD(m.last_name, 1, null) over (partition by m.last_name
order by m.last_name) next_last_name
from master_table m
inner join baseball_players hp on (lower(trim(m.last_name)) = lower(trim(hp.last_name))
and lower(trim(m.first_name)) = lower(trim(hp.first_name)))
order by m.last_name;
0
Comment
Question by:claghorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Expert Comment

by:FutureDBA-
ID: 39825858
can you please give an example of your expected results instead of the explanation?

thanks
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 39826214
Hi!

This query will eliminate all duplicate rows from your result in the main_table
select m.id,  m.last_name, m.first_name
from master_table m, (
select lastname,firstname, count(*)
from master_table
group by lastname,firstname
having count(*)=1) x 
where m.last_name = x.last_name
and m.first_name = x.first_name
order by m.last_name

Open in new window


Then to join that result with the baseball_players table you simply add
select m.id,  m.last_name, m.first_name
from master_table m, (
select lastname,firstname, count(*)
from master_table
group by lastname,firstname
having count(*)=1) x , baseball_players hp
where m.last_name = x.last_name
and m.first_name = x.first_name
and (lower(trim(m.last_name)) = lower(trim(hp.last_name))
and lower(trim(m.first_name)) = lower(trim(hp.first_name)))
order by m.last_name

Open in new window


Hope this helps.

Regards,
    Tomas Helgi
0
 

Author Closing Comment

by:claghorn
ID: 39826374
Thank You.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question