Min dates from group by postgresql

Question

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

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

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

Answer 1

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.

Answer 2

Use row_number() + filter rn=1:

select market,  certificate_id,   date
from 
(
select market,  certificate_id,   date,
       row_number() over(partition by market order by date) rn
  from mytable
) s 
where rn = 1

Answer 3

You can do it using window function row_number() to order records by audit_ending per market.

You can also use rank() instead of row_number() if you have ties and you want to return them all (exemple a market with same two lowest audit_ending ).

select *
from (
  select *, row_number() over (partition by market order by audit_ending) as rn
  from mytable
) as s
where rn = 1;

Demo here