Sql query to display output with interval of 5 seconds

Question

1.00/5 (1 vote)

See more:

What is the simplest way or approach in SQL query to achieve the output below?
Output only display records that are in 5 seconds interval in time_stamp column.
Thanks in advance.

TABLE

date	   time_stamp	values
2021-06-03	10:02:22	200
2021-06-03	10:02:23	199
2021-06-03	10:02:24	198
2021-06-03	10:02:25	197
2021-06-03	10:02:26	196
2021-06-03	10:02:27	195
2021-06-03	10:02:28	192
2021-06-03	10:02:29	193
2021-06-03	10:02:30	199
2021-06-03	10:02:31	191
2021-06-03	10:02:32	199
2021-06-03	10:02:33	195
2021-06-03	10:02:34	194
2021-06-03	10:02:35	196
2021-06-03	10:02:36	198
2021-06-03	10:02:37	190

OUTPUT:

date	   time_stamp	values
2021-06-03	10:02:22	200
2021-06-03	10:02:27	195
2021-06-03	10:02:32	199
2021-06-03	10:02:37	190

What I have tried:

Tried the row_number & mod(%) function combination but it doesn't capture the first row.

Posted 23-Aug-21 22:39pm

Zerk Erotz

Updated 24-Aug-21 0:52am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2021-08-24T00:52:00

Solution 1

If there's no gaps and islands...

Try this:

SQL

SELECT T.*
FROM
(
  SELECT *, (ROW_NUMBER() OVER(ORDER BY ddate, time_stamp)-1) % 5 AS Id
  FROM tablename
) T
WHERE Id=0;

See: SQL Fiddle[^]

Posted 24-Aug-21 0:52am

Maciej Los

Comments

Wendelius 24-Aug-21 11:30am

5ed

Maciej Los 25-Aug-21 0:18am

Thank you, Mika.

Zerk Erotz 24-Aug-21 23:29pm

Great Maciej Los! Thank you so much for the solution.

Maciej Los 25-Aug-21 0:18am

You're very welcome.