Exclude Data in Oracle Select Statment

Posted on 2011-09-27
Last Modified: 2012-05-12
I need to run a select statement that exluces any records that have only 1 version and exclude any records Max(version) where Remvoved = 'F'

Data example is (name, version, removed) table is Analysis
XYZ, 1, F
ABC, 1, F
ABC, 2, F
ABC, 3, F
MNO, 1, F
MNO, 2, F

The value returned should be
ABC, 1, F
ABC, 2, F
MNO, 1, F
Question by:gilnari
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
  • 5
  • 2
LVL 74

Expert Comment

ID: 36710058
>>>exclude any records Max(version) where Remvoved = 'F'

but all 3 of your expected values have removed = 'F'

why are they there if you're supposed to exclude them?
LVL 74

Expert Comment

ID: 36710061
ah, nevermind I figured it out, you want to exlude ONLY the max that is F
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36710079
Is this technically a duplicate of your other question:
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 36710087
S ELECT   name, version, removed
    FROM (SELECT a.*,
                 ROW_NUMBER() OVER (PARTITION BY name ORDER BY version DESC) rn,
                 COUNT(*) OVER (PARTITION BY name) cnt
            FROM analysis a)
   WHERE cnt > 1 AND NOT (rn = 1 AND removed = 'F')
ORDER BY name, version

Author Comment

ID: 36710288
Actaull the reference is one set of the issues this is other set where table was not update and the lesser version where never set as removed so I need to take the select statement and set removed to T.

so Sdstuber  how would I chnage this select statement to an update statement?
LVL 74

Expert Comment

ID: 36710319
>>> how would I chnage this select statement to an update statement?

first you'll have to specify what do you want to update, and what you want the new values to be.

also,  that should be a new question, since it's a new requirement.

you can use the "ask a related question" link to tie them together if you want

Author Comment

ID: 36710702
By the way you guys are great.  You real came through for me in a pinch,
LVL 74

Expert Comment

ID: 36710736
glad to help, and thank YOU for the kudos,  it's always nice to hear we're appreciated.

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use of Exception to end a Loop 3 54
Oracle Insert not working 10 48
Procedure syntax 5 50
error in oracle form 11 28
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

726 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