How to select other rows in GROUP BY

Sorry for the bad title. I don’t know how to summarize it better. I have a table with unix timestamps and other values, like the following.

time	value
1620916380	110
1620916440	100
1620916500	120
1620916560	120
1620916660	90

The entries are always one minute apart. I want to group entries into 5-minute blocks with the value averaged. Currently I use this:

SELECT time, avg(value)
FROM table
GROUP BY CEIL(time/ 300)
ORDER BY time ASC;

The result:

time	avg(value)
1620916380	108

So, for time the first occurrence is shown. I want the last to be shown:

time	avg(value)
1620916660	108

How can I do that? I use MariaDB.

Answer

Don’t you just want MAX(time)?

SELECT MAX(time) AS time
     , avg(value)
  FROM table
 GROUP BY CEIL(time/ 300)
 ORDER BY time ASC
;

For completeness, we should mention that the original query is not valid (per standard SQL), even though MariaDB can be configured to allow it.

The reason is time in the select list is not functionally dependent on the GROUP BY terms CEIL(time/300).

Selecting MAX(time) AS time in the solution corrects the above functional dependence issue, and also corrects a similar issue when trying to use ORDER BY time, which was also a problem in the original query.