Solved

Select unique records only.

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
run a PARFILE from a Windows Scheduled task - Oracle export 5 40
Oracle Pivot 2 43
Oracle - Stored Procedure Privilge access 3 38
data lookup in Oracle - need suggestions 55 102
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 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

16 Experts available now in Live!

Get 1:1 Help Now