Oracle/SQL - Finding records with one value excluding nulls
NickName:Ryan Nalls Ask DateTime:2015-03-21T17:02:05

Oracle/SQL - Finding records with one value excluding nulls

I have a question very similar to another question but I can't quite figure it out. Here is the link to the original question:Oracle/SQL - Finding records with one value excluding by similar record

So similar to that problem, I have records that will either have a 1 or null. the same records can be a combination of 1 or null and in those instances, I want to exclude the record altogether. For example:

    Person  Type
    --------------
    Bob     1
    Sue     1
    Bob     null
    Tom     1
    Frank   1
    Frank   null
    Fred    null

I want the following returned:

    Person  Type
    --------------
    Sue     1
    Tom     1

Any direction on this would be very much appreciated. I dont have much time to solve this so even speaking conceptually will help!

The closest I came was

    select person from table
    where type = 'S'
    MINUS
    select person from table
    where type is null

But of course that doesnt work.

I can write a function if that is the only way. Thank you!

Copyright Notice:Content Author:「Ryan Nalls」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/29180957/oracle-sql-finding-records-with-one-value-excluding-nulls

Answers
Mark 2015-03-21T09:17:22

Try this:\n\nselect person, type from table\nwhere type = '1'\nand person not in (select person from table where type is null)\n",


a_horse_with_no_name 2015-03-21T09:39:50

Apart from Mark's NOT IN approach, this can also be written as a NOT EXISTS condition:\n\nselect p1.person\nfrom person p1\nwhere p1.type = 1\nand not exists (select 1 \n from person p2\n where p1.person = p2.person\n and p2.type is null)\norder by p1.person;\n\n\nIt essentially says: get me every person where type is 1 but where there is no other row for this person where the type is null.\n\nSQLFiddle example: http://sqlfiddle.com/#!4/7623c/4",


More about “Oracle/SQL - Finding records with one value excluding nulls” related questions

Oracle/SQL - Finding records with one value excluding nulls

I have a question very similar to another question but I can't quite figure it out. Here is the link to the original question:Oracle/SQL - Finding records with one value excluding by similar recor...

Show Detail

SQL Finding records with one value

I'm a newbie on SQL hence my limitation on coding it. This question is similar to this previous questions: SQL Finding Duplicate Values of Rows Where Another Field has a Value and Oracle/SQL - Find...

Show Detail

SQL Server equivalent to Oracle's NULLS FIRST?

So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order: ORDER BY date_sent NULLS FIRST What is comparable in SQL Server? ...

Show Detail

Last_value with IGNORE NULLS in SQL Server

I have a time series that with null values. I want to be replace each null value with the most recent non-non value. From what I've researched, Oracle SQL can easily accomplish this using Last_va...

Show Detail

Compare millions of records from Oracle to SQL server

I have an Oracle database and a SQL Server database. There is one table say Inventory which contains millions of rows in both database tables and it keeps growing. I want to compare the Oracle table

Show Detail

Is there a PDO::ATTR_ORACLE_NULLS equivalent for INSERT statements?

I've got a function that reads data from one database, formats and recombines them a bit and inserts them into another. However, any line with missing data for certain columns should be rejected by...

Show Detail

Can't test nulls in Oracle Pro*C

I am beginner about Proc Oracle and C. When I practice example base on Oracle Proc document, I can't test Nulls which use indicator, reference Oracle Pro*c guide link(I am not sure my code is corre...

Show Detail

SQL count nulls and non nulls

Apologies if there is already an answer out there for this, I couldn't find it anywhere! I want to create a SQL query (in Oracle) displays a list of all A, B, C rows, example below, where there ar...

Show Detail

Auto-deleting particular records in one table of Oracle database using SQL

I've got question concerning auto deleting particular records in one table of Oracle database using SQL. I am making small academic project of database for private clinic and I have to design Oracle

Show Detail

Display a count of records that are not nulls

Most of the records in the report in the field VOID are nulls (no display) I do not know where to begin, so there is nothing to show that i have tried. How do i present a summary count of records i...

Show Detail