I try to get some rows from a table where I group by one value but the other columns I want to get back are not in the group by or any aggregate clause. It works fine on sqlite but does not work on postgresql.

I would all rows grouped by name where created is max for that name. E.g:

The problem is that I cannot use MAX in a where clause, nor can I just group by name as SQL complains that I need to group by value2 as well, which I don't want. I want just the value from the row with the max value1 grouped by name.

Or in other words: I want one row per name, which is selected based on the max value1 with an additional column that is the sum of all value2 for that name.

The closest I got is:

SELECT MAX(value1), created, value2, SUM(value2), name
FROM table
GROUP BY name

But obviously that does not work. Any help is welcome!

CodePudding user response：

Might be a couple options here but you can do this with a CTE like the following:

WITH agg_data AS
(
SELECT name
     , MAX(value1) maxval1
     , SUM(value2) sumval2
  FROM t
 GROUP BY name
)
SELECT ad.*
     , t2.created
     , t2.value2
  FROM agg_data ad
  JOIN t t2 ON t2.value1 = ad.maxval1 AND t2.name = ad.name
;

Here is a DBFiddle to show a working example.

CodePudding user response：

You could use a subquery:

SELECT m.maxvalue1, t.created, t.value2, m.sumvalue2, t.name
FROM table t
inner join
(
   SELECT MAX(value1) as maxvalue1, SUM(value2) as sumvalue2, name
   FROM table
   GROUP BY name
) m
on m.value1 = t.value1 and m.name = t.name

CodePudding user response：

with data as (
    select *, row_number() over (partition by name order by created desc) rn
    from T
)
select max(created) as created, max(value2) filter (where rn = 1) as value2, name
from data
group by name;

You could also use this inside the CTE:

first_value(value2) over (partition by name order by created desc) as value2