• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Exclude Data in Oracle Select Statment

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
0
gilnari
Asked:
gilnari
  • 5
  • 2
1 Solution
 
sdstuberCommented:
>>>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?
0
 
sdstuberCommented:
ah, nevermind I figured it out, you want to exlude ONLY the max that is F
0
 
slightwv (䄆 Netminder) Commented:
Is this technically a duplicate of your other question:
http://www.experts-exchange.com/Database/Oracle/Q_27343374.html
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
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
0
 
gilnariAuthor Commented:
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?
0
 
sdstuberCommented:
>>> 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
0
 
gilnariAuthor Commented:
By the way you guys are great.  You real came through for me in a pinch,
0
 
sdstuberCommented:
glad to help, and thank YOU for the kudos,  it's always nice to hear we're appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now