[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select unique records only.

Posted on 2014-01-31
3
Medium Priority
?
583 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 26

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 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