Solved

Selecting only those records where there is only one row - Oraclw query

Posted on 2014-10-23
1
367 Views
Last Modified: 2014-10-27
Experts,

I am reading a table in Oracle:
      Select   t1.CLAIM_NO
                              , t1.KICKOUT_CODE
                  , t1.cert_no
                   , t1.cost_center
                  , t1.arrv_date
       From Table1;

I would like to select only those records where there is only one row where t1.claim_no, t1.cert_no are the same.

Does anyone know how/if this can be coded as a single query?
0
Comment
Question by:morinia
[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
1 Comment
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40399879
Try this:
      Select   CLAIM_NO
                   ,KICKOUT_CODE
                   ,cert_no
                   ,cost_center
                   ,arrv_date
from(
      Select   t1.CLAIM_NO
                   , t1.KICKOUT_CODE
                   , t1.cert_no
                    , t1.cost_center
                   , t1.arrv_date
                  , count(*) over(partition by claim_no, cert_no) mycount
        From Table1
)
where mycount=1;
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 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