sql: Select count(*) - nth record from each group

0
votes

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
2
sqlcockroachdb
I think you just want to use row_number() here -- but I'm not sure what you need. you want every record after the 1000th one? - Hogan
Say there are 1014 records under a give tenant_id. I want to (ultimately) delete the oldest 14 records so that there are only 1000 records for the tenant in question. Therefore I'm getting the timestamp for the (1014 - 1000)th record, then planning to use smaller than for my actual delete. @Hogan - Jobs
you want all the ids after the 1000th one? - Hogan
Not quite. I want the (total per tenant - 1000)th timestamp. Just one timestamp. @Hogan - Jobs
@Hogan Edited the question to clarify. - Jobs

2 Answers

1
votes

You seem to want ROW_NUMBER(). Cockroach supports windows functions, so:

SELECT updated
FROM (
    SELECT
        tenant_id, 
        updated,
        ROW_NUMBER() OVER(PARTITION BY tenant_id ORDER BY updated DESC) rn
    FROM trace
) x WHERE rn = 1001

For each tenant_id, this will return the timestamp of the 1001th less recent record. If a given tenant has less than 1000 records, it will not appear in the results.

1
votes
select x.tenant_id
from (
  select t.tenant_id,
         row_number() over (partition by t.tenant_id order by t.timekey) as tenant_number
  from fgc.trace as t
) x
where x.tenant_number > 1000
group by x.tenant_id

just the one timestamp would look like this:

select min(x.timekey) as min_timestamp
from (
  select t.tenant_id, t.timekey,
         row_number() over (partition by t.tenant_id order by t.timekey) as tenant_number
  from fgc.trace as t
) x
where x.tenant_number > 1000

note that grouping does not matter here because each row can only be in one group and you are only looking at one row.