How to remove records with blank values based on existence in specific column?

I want to remove all records with blank values in prev and next - but only for cases where the curr value still exists elsewhere. For example,

Input:

prev   curr   next   rand_value
       B             1231
       B             323
A      B             3232
       B      C      3233
       D             12313

Output:

prev   curr   next   rand_value
A      B             3232
       B      C      3233
       D             12313

My code so far:

SELECT * 
FROM my_table
WHERE prev IS NOT NULL
OR next IS NOT NULL
-- but this doesn't catch the final row
-- maybe use something like EXISTS?

CodePudding user response：

You can use NOT EXISTS:

SELECT t1.* 
FROM tablename t1
WHERE t1.prev IS NOT NULL 
   OR t1.next IS NOT NULL
   OR NOT EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE t2.curr = t1.curr AND (t2.prev IS NOT NULL OR t2.next IS NOT NULL)
      );

See the demo.

CodePudding user response：

each unique rand_value will get a row number 1 to X... ordered by prev, next value. If both are "NULL" it will get a row_number of 1. Since nulls appear first we have to assign a descending order.

Perhaps 1 way....

WITH CTE AS (
SELECT *, row_number() over (partition by rand_value order by prev DESC, next DESC) RN
FROM my_table)

SELECT * FROM CTE WHERE RN =1

Alternative way: Get all that are not null, then get all nulls union...

SELECT * 
FROM my_table 
WHERE prev IS NOT NULL
   OR next IS NOT NULL  

UNION ALL

SELECT * 
FROM my_table
WHERE prev IS NULL
  AND next is NULL