• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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