Sql select with Null and Not Null values

Question

I have a table like below.

I wanted to select the spec_id with language is not null. But when a spec_id has no values other than null then i wanted to select that spec_id with null value as well. For eg. Spec_id '170470' has Language 'EN'. I wanted to select only that row with Language is not null for this spec_id. And spec_id '170464' has only Null value. So i wanted to select that row as well.

I have tried with below query but it selects only that case with NOT NULL in Language.

   SELECT * from temp_value w1 
    where w1.NAC_ID = 2453 
    and w1.language is not null 
    or (w1.language is null and not exists (select spec_id from temp_value w2
                                        where w2.nac_id = 2453 
                                        and spec_id  in (select spec_id from temp_value 
                                                           where nac_id = 2453 and language is not null))
         and w1.nac_id = 2453);

Answer 1

I think we can actually simplify this by using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY spec_id ORDER BY language) rn
    FROM temp_value t
)

SELECT language, value, value_id, spec_id, language_id, nac_id
FROM cte
WHERE rn = 1;

This should work because Oracle by default sorts NULL last. This means that, for each group of spec_id records, the non NULL language value would float to the top, if it exists. Only if a given spec_id group have no non NULL language records would a NULL record be selected.

Edit:

To cater to the problem where there might be two or more non NULL language records, with the previous logic for retaining a NULL only should there be no non NULL records, we can try:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY spec_id ORDER BY language) rn
    FROM temp_value t
)

SELECT language, value, value_id, spec_id, language_id, nac_id
FROM cte
WHERE language IS NOT NULL OR (rn = 1 AND language IS NULL);

Answer 2

You can also just use a correlated subquery: in the subquery you refer to the table alias of the main query.

select * from temp_value t
where language is not null
or language is null and not exists (select 'x'
                                    from temp_value
                                    where spec_id = t.spec_id
                                    and nac_id = t.nac_id
                                    and (language is not null
                                         or value is not null
                                         or language_id is not null));