SQL - Get value based on a condition per entity-CodePudding

I have a table defined as :

Entity	Version	Value1	Value2
a	current	null	null
a	last_year	50	100
b	current	25	100
c	current	40	100
c	last_year	null	null
d	current	50	100
d	last_year	55	200

I want to extract Value1 and Value2 for each entity whenever there is a value but preference for 'Version = Current'. So, for the above example, the answer should be:

Entity	Version	Value1	Value2
a	last_year	50	100
b	current	25	100
c	current	40	100
d	current	50	100

CodePudding user response：

This is a typical use-case for row_number, the only complication being the ordering criteria.

Assuming You want rows with both values being null to have the lowest rank, try the following:

select Entity, Version, Value1, Value2
from (
    select *,
        Row_Number() over(partition by entity 
                          order by 
                            case when Concat(value1,value2) = '' then 1 else 0 end,
                         version) rn
    from t
)t
where rn = 1;

See Demo Fiddle