Solved

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

Posted on 2014-10-23
1
352 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
1 Comment
 
LVL 76

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 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