2

I have the query : 

select A.*, A.DocumentID.DocId, D.Key, D.Value
from `serv.dam.events` A  
left join unnest (A.metadata) D 
where A.Creationtimestamp > '2018-10-01' 
order by Creationtimestamp desc 
limit 10000

I want to limit the values per day to 10000. How can i do it?

Bulat
  • 6,869
  • 1
  • 29
  • 52
claudiu lujanschi
  • 61
  • 1
  • 6
  • Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Bulat Oct 16 '19 at 06:44

1 Answers1

3

You can use row_number() to enumerate the rows:

select *
from (select e.*, e.DocumentID.DocId, D.Key, D.Value,
             row_number() over (partition by date(a.creationtimestamp) order by rand()) as seqnum
      from `serv.dam.events` e left join
           unnest (A.metadata) D 
      where A.Creationtimestamp > '2018-10-01' 
     ) e
where seqnum <= 1000
order by Creationtimestamp desc
claudiu lujanschi
  • 61
  • 1
  • 6
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786