Is there a way to get first row of a group in postgres based on Max(date)

Question

Input :

id      name        value1      value2        date

1        A              1           1       2019-01-01
1        A              2           2       2019-02-15
1        A              3           3       2019-01-15
1        A              1           1       2019-07-13

2        B              1           2       2019-01-01
2        B              1           3       2019-02-15
2        B              2           1       2019-07-13

3        C              2           4       2019-02-15
3        C              1           2       2019-01-01
3        C              1           9       2019-07-13
3        C              3           1       2019-02-15

Expected Output :

id      name        value1      value2        date

1        A              1    Avg(value2)    2019-07-13
2        B              2    Avg(value2)    2019-07-13
3        C              1    Avg(value2)    2019-07-13

Please edit your avg(value2) entries to show the expected average. E.g. is the avg value2 for 3 including or excluding values after the specified date? — Boneist, Nov 20 '19 at 10:48

score 1 · Answer 1 · answered Nov 20 '19 at 10:36

1

You can use window functions. rank() over() can be used to identify the first record in each group, and avg() over() will give you a window average of value2 in each group:

select id, name, value1, avg_value2 value2, date
from (
    select 
        t.*,
        avg(value2) over(partition by id, name) avg_value2,
        rank() over(partition by id, name order by date desc) rn
    from mytable t
) t
where rn = 1

answered Nov 20 '19 at 10:36

GMB

216,147
25
84
135

Thanks that was heplful and How could I do it to year level – Anonymous Nov 20 '19 at 12:26
Like If date is 2019-07-13 value1 = 5 and date is 2019-09-13 value = 3. I need to get max date and value for that date. How could I do it? Output will be :- 2019-09-13 value1 = 3 – Anonymous Nov 20 '19 at 12:28
@Anonymous: yes the query does that. It gives you the record that has the highest date for each id/name tuple. You can try and run it against your real data to see the results by yourself. – GMB Nov 20 '19 at 12:41

score 0 · Answer 2 · answered Nov 20 '19 at 11:19

sort your data in the right way, use the window function row_number() as identifier and select the first entry of every partition.

    with temp_data as 
    (
    select
    row_number() over (partition by debug.tbl_data.id order by debug.tbl_data.date desc) as index,
    *,
    avg(debug.tbl_data.value2)over (partition by debug.tbl_data.id) as data_avg
    from debug.tbl_data 
    order by id asc, debug.tbl_data.date desc
    )

    select 
    *
    from temp_data
    where index = 1

score 0 · Answer 3 · answered Nov 20 '19 at 11:25

0

You seem to want the most common value of value1. In statistics, this is called the "mode". You can do this as:

select id, name,
       mode() within group (order by value1) as value1_mode,
       avg(value2),
       max(date)
from t
group by id, name;

answered Nov 20 '19 at 11:25

Gordon Linoff

1,242,037
58
646
786

Is there a way to get first row of a group in postgres based on Max(date)

3 Answers3