Search code examples
postgresqlpostgresql-11

Selecting where only one unique max record exists in PostgreSQL

I have 2 tables, the first is the t_cuti table and the second table is t_cuti_adjust. Where the second table draws data from the first table.

1st table : t_cuti

enter image description here

And my 2nd table : t_cuti_adjust

enter image description here

This is display data for now: enter image description here

And this is my expexted result:

enter image description here

I have problems when doing a query. I want to only display data from the largest ID with same the unique field nik.

My query:

CREATE OR REPLACE FUNCTION public.fn_daftar_adjust_saldo(
    character varying,
    character varying)
    RETURNS SETOF record 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE 
        rcd record;
        v_rumah_sakit alias for $1;
        v_unit alias for $2;

begin
for rcd in

select
    adj.id as id,
    cuti.nik as nik,
    nm_karyawan as nama_karyawan,
    nm_rs as rumah_sakit,
    nm_unit as unit,
    nm_jabatan as jabatan,
    cuti.saldo_cuti as saldo,
    adj.created_at as created_at
from
 --(select max(id) as id_tr, nik from t_cuti_adjust s group by s.nik ) x
 t_cuti cuti
 left join t_cuti_adjust adj on adj.nik=cuti.nik
 inner join m_karyawan kary on kary.nik=cuti.nik
 inner join m_rumah_sakit rs on rs.kd_rs=cuti.kd_rs
 inner join m_unit u on u.kd_unit=kary.kd_unit
 inner join m_jabatan j on j.kd_jabatan=kary.kd_jabatan

where 
case when v_rumah_sakit='' then 1=1 else nm_rs=v_rumah_sakit end
and case when v_unit='' then 1=1 else nm_unit=v_unit end

loop
return next rcd;
end loop;
/*
select * from fn_daftar_adjust_saldo('','') as (id bigint, nik character(9),nama_karyawan character varying,rumah_sakit character varying, unit character varying,jabatan character varying, saldo smallint, created_at timestamp with time zone)
*/
return;
end
$BODY$;

ALTER FUNCTION public.fn_daftar_adjust_saldo(character varying, character varying)
    OWNER TO postgres;
Solution

  • You can use DISTINCT ON for that. Restricted to the table t_cuti this means in your case:

    SELECT DISTINCT ON(nik) id, nik, saldo_cuti FROM t_cuti
ORDER BY nik, id DESC;

    Sorting by id ensures that the line with the largest value for id is always used. Postgres requires for DISTINCT ON queries with sorting to be sorted according to the attributes of the DISTINCT ON clause, too.

    Your query should look like:

    SELECT DISTINCT ON(cuti.nik)
    adj.id as id,
    cuti.nik as nik,
    nm_karyawan as nama_karyawan,
    nm_rs as rumah_sakit,
    nm_unit as unit,
    nm_jabatan as jabatan,
    cuti.saldo_cuti as saldo,
    adj.created_at as created_at
FROM t_cuti cuti
 left join t_cuti_adjust adj on adj.nik=cuti.nik
 inner join m_karyawan kary on kary.nik=cuti.nik
 inner join m_rumah_sakit rs on rs.kd_rs=cuti.kd_rs
 inner join m_unit u on u.kd_unit=kary.kd_unit
 inner join m_jabatan j on j.kd_jabatan=kary.kd_jabatan

WHERE 
  case when v_rumah_sakit='' then 1=1 else nm_rs=v_rumah_sakit end
  and case when v_unit='' then 1=1 else nm_unit=v_unit end
ORDER BY cuti.nik, adj.id DESC