Search code examples
sqlpostgresqlgroup-bymindistinct-on

Min dates from group by postgresql

I have table with data:

create table measure_app_scalemeasurement (
  store_code text,
  certificate_id text,
  audit_ending date);
insert into measure_app_scalemeasurement values
('K010','vwv',  '10.12.2023'),
('K010','cert1','12.12.2023'),
('K054','vwv',  '14.12.2023'),
('K054','cert1','20.01.2024');

I want to select min audit_ending date for each market store_code, also getting its corresponding cert_id

Here's a query I tried

select store_code, certificate_id, min(audit_ending) from measure_app_scalemeasurement
group by store_code, certificate_id

pic1

But it is giving all markets store_code, and this query:

select distinct on (store_code) store_code, certificate_id, min(audit_ending) from measure_app_scalemeasurement
group by store_code, certificate_id

pic2

it gave min date for market K010, but did not for market K054. For K054 it is giving max date.

I want to get first min date by markets with its certificate id like this:

store_code certificate_id audit_ending
K010 vwv 10.12.2023
K054 vwv 20.01.2024
Solution

  • Your distinct on was almost right: demo

    select distinct on(store_code) store_code, certificate_id, audit_ending
from measure_app_scalemeasurement
order by store_code, audit_ending;
    store_code certificate_id audit_ending
    K010 vwv 2023-12-10
    K054 vwv 2023-12-14

    You just needed a matching order by so that for each store_code it only keeps the earliest (min) date.