Question 296305 in PostgreSQL Question posted in
The official documentation can be found here.

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search