CODEWITHSUNDEEP
sqlpostgresql
Chiefir
Chiefir

Reputation: 2671

Multiple select of max values in PostgreSQL for specific ID

I have a table like this:

ID  cbk           due_16_30        due_31_60 
  1  2018-06-19    5                200
  2  2018-06-19    100              -5
  1  2018-06-19    -2               2
  2  2018-06-18    20               Null
  2  2018-06-18    50               22
  1  2018-06-18    30               150
  3  2018-06-18    20               70

I want to select for each specific ID a max due_16_30 and a max due_31_60 from the latest date, where date is between some start date and end date. How can I do that in PostgreSQL?

P.S. This is how the 2nd part is solved: https://stackoverflow.com/a/51493567/8495108

Upvotes: 0

Views: 40

Answers (4)

Chiefir
Chiefir

Reputation: 2671

Solved like this (simplified):

SELECT v.id, max(v.due_31_60), max(v.due_61_90), v.cbk
      FROM my_table as v
      JOIN (select id, max(cbk) as max_date from my_table
           WHERE (cbk >= start_date and cbk <= end_date ) 
           GROUP BY id) as q 
      ON q.id = v.id and v.cbk = q.cbk
      GROUP BY v.id, v.cbk

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

One method uses distinct on:

select distinct on (id) id, max(due_16_30), max(due_31_60) 
from t
where date >= ? and date < ?
group by id, date
order by id, date desc;

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

You need to consider myID parameter for both subquery and outer query as :

select ID,
       cbk,
       max(due_16_30) as max_due_16_30, 
       max(due_31_60) as max_due_31_60
  from tab
 where cbk in 
(
  select max(cbk)
    from tab 
   where cbk between start_date and end_date
     and ID = myID
)
   and ID = myID
 group by ID, cbk;

We may try for id = 3 in the demo, since there's no id = 3 for the latest date 2018-06-19 for whole table :

DB-Fiddle Demo

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do :

select t.id, t.cbk, max(t.due_16_30), max(t.due_31_60)
from table t
where cbk = (select t1.cbk
             from table t1
             where t1.cbk >= start_dt and t1.cbk <= end_dt
             order by t1.cbk desc
             limit 1
            )
group by t.id, cbk
order by t.id desc
limit 1;

Upvotes: 0

Related Questions