Solved

Select unique records only.

Posted on 2014-01-31
3
549 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
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 24

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now