How to get id of the row which was selected by aggregate function?

Question

I have next data:

id | name | amount | datefrom
---------------------------
3  | a    |  8     | 2018-01-01
4  | a    |  3     | 2018-01-15 10:00
5  | b    |  1     | 2018-02-20

I can group result with the next query:

select name, max(amount) from table group by name

But I need the id of selected row too. Thus I have tried:

select max(id), name, max(amount) from table group by name

And as it was expected it returns:

id | name | amount
-----------
4  | a    |  8
5  | b    |  1

But I need the id to have 3 for the amount of 8:

id | name | amount
-----------
3  | a    |  8
5  | b    |  1

Is this possible?

PS. This is required for billing task. At some day 2018-01-15 configuration of a was changed and user consumes some resource 10h with the amount of 8 and rests the day 14h -- 3. I need to count such a day by the maximum value. Thus row with id = 4 is just ignored for 2018-01-15 day. (for next day 2018-01-16 I will bill the amount of 3)
So I take for billing the row:

3  | a    |  8     | 2018-01-01

And if something is wrong with it. I must report that row with id == 3 is wrong.

But when I used aggregation function the information about id is lost.

Would be awesome if this is possible:

select current(id), name, max(amount) from table group by name
select aggregated_row(id), name, max(amount) from table group by name

Here agg_row refer to the row which was selected by aggregation function max

UPD
I resolve the task as:

SELECT 
    (
       SELECT id FROM t2 
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount ) 
    ) id,
    name, 
    MAX(amount) ma,
    SUM( ratio )
FROM t2  tf
GROUP BY name

UPD
It would be much better to use window functions

Answer 1

There are at least 3 ways, see below:

CREATE TEMP TABLE test (
    id integer, name text, amount numeric, datefrom timestamptz
);

COPY test FROM STDIN (FORMAT csv);
3,a,8,2018-01-01
4,a,3,2018-01-15 10:00
5,b,1,2018-02-20
6,b,1,2019-01-01
\.

Method 1. using DISTINCT ON (PostgreSQL-specific)

SELECT DISTINCT ON (name)
  id, name, amount
FROM test
ORDER BY name, amount DESC, datefrom ASC;

Method 2. using window functions

SELECT id, name, amount FROM (
  SELECT *, row_number() OVER (
    PARTITION BY name
    ORDER BY amount DESC, datefrom ASC) AS __rn
  FROM test) AS x
WHERE x.__rn = 1;

Method 3. using corelated subquery

SELECT id, name, amount FROM test
WHERE id = (
  SELECT id FROM test AS t2
  WHERE t2.name = test.name
  ORDER BY amount DESC, datefrom ASC
  LIMIT 1
); 

Answer 2

demo: db<>fiddle

You need DISTINCT ON which filters the first row per group.

SELECT DISTINCT ON (name) 
    * 
 FROM table 
 ORDER BY name, amount DESC

Answer 3

You need a nested inner join. Try this -

SELECT id, T2.name, T2.amount
FROM TABLE T
INNER JOIN (SELECT name, MAX(amount) amount
            FROM TABLE
            GROUP BY name) T2
ON T.amount = T2.amount