Use of count in where statement sql-CodePudding

I have N transactions with camera_id = 6 and i want to sample every N // 100 transaction. I have the following query:

SELECT t.id from (
    SELECT id, camera_id, start_ts, ROW_NUMBER() OVER (ORDER BY start_ts) AS rownum
    FROM transactions
    WHERE camera_id = 6  
) as t
where t.rownum % (N / 100) = 1
order by t.start_ts

How can i change it so i don't need additional query for determining N?

CodePudding user response：

Untested

Does the following work for you - add a windowed count in addition to your Rownumber and use that:

SELECT t.id from (
    SELECT id, camera_id, start_ts, 
            Row_Number() OVER (ORDER BY start_ts) AS rownum,
            Count(*) over() Qty
    FROM transactions
    WHERE camera_id = 6  
) as t
where t.rownum % (Qty / 100) = 1
order by t.start_ts