Solved

Select unique records only.

Posted on 2014-01-31
3
571 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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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