CODEWITHSUNDEEP
sqloracle-databaseselect
George J
George J

Reputation: 11

How to to give preference to null value during select

I have a table with

Id      value 
1000    null
1000    En
1000    Fr
1000    Es
1001    En
1001    Fr
1001    Es

Output of the select query should be as follows. (Since 1000 has a null value only, select the row with null value) 

Id      value
1000    null
1001    En
1001    Fr
1001    Es

Upvotes: 1

Views: 155

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In the most recent versions of Oracle, you can actually do this without a subquery:

select t.*
from t
order by rank() over (partition by id order by (case when value is null then 1 else 2 end))
fetch first 1 row with ties;

Here is a db<>fiddle.

Upvotes: 2

user5683823
user5683823

Reputation: 

with
  t (id, value) as (
    select 1000, null from dual union all
    select 1000, 'En' from dual union all
    select 1000, 'Fr' from dual union all
    select 1000, 'Es' from dual union all
    select 1001, 'En' from dual union all
    select 1001, 'Fr' from dual union all
    select 1001, 'Es' from dual
  )
select id, value
from   (
         select t.*, 
                dense_rank() over (partition by id order by nvl2(value, 1, 0)) rnk
         from   t
       )
where  rnk = 1
;

        ID VA
---------- --
      1000   
      1001 En
      1001 Fr
      1001 Es

Functions used in this query:

NVL2() https://docs.oracle.com/database/121/SQLRF/functions132.htm#SQLRF00685

DENSE_RANK() https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm

Upvotes: 2

Popeye
Popeye

Reputation: 35930

You can use analytical function as following:

Select id , value from
(Select t.*,
       Coalesce(Sum(case when value is null then 1 end) over (partition by id), 0) as cnt
 From your_table)
Where (cnt = 1 and value is null)
    or cnt = 0

Cheers!!

Upvotes: 1

sticky bit
sticky bit

Reputation: 37507

You can use NOT EXISTS and a correlated subquery to check for the non-existence of a NULL for an ID. Include these rows and also rows where value is NULL.

SELECT t1.id,
       t1.value
       FROM elbat t1
       WHERE NOT EXISTS (SELECT *
                                FROM elbat t2
                                WHERE t2.id = t1.id
                                      AND t2.value IS NULL)
              OR t1.value IS NULL;

Upvotes: 3

Related Questions