Wednesday, September 4, 2019

PostgreSQL: distinct but only for one column

Source: Postgres: Distinct but only for one column
Problem: The following returns 2 distinct network_id for the same mccmnc_code.
with toto as (
    select mccmnc_code from (values (24024)) as x(mccmnc_code)
)
select dt_mccmnc.mccmnc_code, dt_mccmnc.network_id, dt_mccmnc.country_id
from toto
left join shared_db.dt_mccmnc 
    on dt_mccmnc.mccmnc_code = toto.mccmnc_code
    and dt_mccmnc.master_country_flg is true
    and localtimestamp(0) <@ dt_mccmnc.validity_period

-- mccmnc_code network_id country_id
-- 24024 1828 297
-- 24024 1816 297
So as to keep only one of the rows, use the following:
with toto as (
    select mccmnc_code from (values (24024)) as x(mccmnc_code)
)
select distinct on (dt_mccmnc.mccmnc_code) dt_mccmnc.mccmnc_code, dt_mccmnc.network_id, dt_mccmnc.country_id
from toto
left join shared_db.dt_mccmnc 
    on dt_mccmnc.mccmnc_code = toto.mccmnc_code
    and dt_mccmnc.master_country_flg is true
    and localtimestamp(0) <@ dt_mccmnc.validity_period

-- mccmnc_code network_id country_id
-- 24024 1828 297