Sample every nth record in DB based on column

Question

Hey Everyone,

I'm trying to sample every nth record from a column in a DB with timestamps in it. For example if I wanted to specify that I wanted to sample every 10 seconds starting with the first row - what's the most efficient way to do this? I tried the sample in DB tool but that only allows for percentage/ number of records.

My data set looks like this -

msg iddatatimestamp_sinput_timestamp_scvdt35_bus_r1290x00000000000000004/24/2018 16:20152458680721290x00000000000000004/24/2018 16:20152458680821290x00000000000000004/24/2018 16:20152458680921290x00000000000000004/24/2018 16:20152458680621290x00000000000000004/24/2018 16:20152458681021290x00000000000000004/24/2018 16:20152458681121290x00000000000000008/14/2018 3:13153421638821290x00000000000000008/14/2018 3:1315342163892

MarqueeCrew · Accepted Answer

@shivraj_r,

Using either logic looking for specific seconds (e.g. in (10,20,30)) or using a MOD function (b = mod( a , m ) returns the remainder after division of a by m , where a is the dividend and m is the divisor) I would filter the data to get only the records that hit those marks.  Depending upon your database, the SQL will vary as not all SQL is the same.

Random is easier if you can assign a random number (SQL varies) and pull out the random data that matches your % or value.

Cheers,

Mark