MySQL GROUP BY how to get data from first element

Question

I have next table with data:

And I run this query:

select *, UNIX_TIMESTAMP(`time`) AS `timeu`, AVG(`value`) AS `valuea`
from `values` 
group by `currency`, DATE(`time` - interval 7 day) 
order by `id` asc 

In result I get

As you can see ids are: 1, 2, 3, 4, 11, 12. I want to have 1, 2, 3, 4, 5, 6 - how can I simply build my query to do that?

Answer 1

You would not do a group by.

I suspect you can do what you want using a correlated subquery:

select v.*, UNIX_TIMESTAMP(`time`) AS `timeu`,
      (SELECT AVG(`value`)
       from values v2
       where v2.currency = v.currency and
             v2.time >= v2.time - interval 7 day
      ) as `valuea`
from `values` v
order by `id` asc ;