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

That tiny image text is hard to read on my small laptop screen. And I can't copy-and-paste from images. — jarlh
– jarlh, Commented Dec 1, 2023 at 9:16
First, post the code and results as text. Images can't be copied and tested. Second, clarify what you want. It seems like you want to get the earliest certificate per market by date. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 1, 2023 at 9:19
And please use the same column names in the queries. There's no audit_ending in the question itself and no date in the images — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 1, 2023 at 9:20

Zegarek · Accepted Answer · 2023-12-01 09:14:56Z

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.

edited Dec 1, 2023 at 9:14

answered Dec 1, 2023 at 9:08

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zegarek Over a year ago

A regular distinct on is also simpler and faster than emulating it through the use of row_number()over(): demo

SelVazi · Accepted Answer · 2023-12-01 09:13:58Z

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

edited Dec 1, 2023 at 9:13

answered Dec 1, 2023 at 9:08

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Comments

leftjoin · Accepted Answer · 2023-12-01 09:39:10Z

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

edited Dec 1, 2023 at 9:39

answered Dec 1, 2023 at 9:08

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

The from clause is missing from the inner query

Collectives™ on Stack Overflow

Min dates from group by postgresql

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related