I'm grouping by tenant\_id. I want to select the count(*) - 1000th record (ordered by \_updated time) from each GROUPBY group, for the groups where count(*) is greater than 1000. As follows:

```
select t1.tenant_id,
(select temp._updated
 from trace temp
 where temp.tenant_id = t1.tenant_id
 order by _updated limit 1 offset   
    count(*) - 1000
) as timekey 
from fgc.trace as t1
group by tenant_id 
having count(*)  > 1000;

```

But this is not allowed as count(\*) cannot be used inside the subquery.

So I tried the following, which still doesn't work as I don't have access to t1 since this is not a join.

```
select t1.tenant_id,
(select temp._updated
 from trace temp
 where temp.tenant_id = t1.tenant_id
 order by _updated limit 1 offset   
    (select count(*)-1000 
     from trace t2
     group by tenant_id 
     having t2.tenant_id = t1.tenant_id)
) as timekey 
from fgc.trace as t1
group by tenant_id 
having count(*)  > 1000;

```

So how can I get the following?

```
  tenant_id |             timekey               
+-----------+----------------------------------+
  n7ia6ryc  | 2019-07-23 23:09:49.951406+00:00  

```