SQL select every xth row based on total number
Work with ROW_NUMBER(), which is a "window function":
SELECT s.* FROM ( SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.timestamp) as rnk, COUNT(*) OVER() as total_cnt FROM gps t WHERE t.timestamp between '2016-12-12T02:00:00Z' AND '2016-12-12T03:00:00Z') sWHERE MOD(s.rnk,(total_cnt/1000)) = 0
An alternative is to randomize the data and then use limit:
SELECT *FROM gpWHERE timesamp >= '2016-12-12T02:00:00Z' AND timestamp <= '2016-12-12T03:00:00Z'ORDER BY random()LIMIT x;This doesn't guarantee an exact distribution across all timestamps. But, it does make it possible to get exactly 1000 values.
Assuming your limit is 1000 and you already know the totalRowCount this may also work, calculate "divisor" first like totalRowCount/1000 then use it
select * from (select *, row_number() over() from gps where ...) as mysubquerywhere row_number % your_divisor = 0If you want it sorted you can add that within the over() parens.