Click here to Skip to main content
15,125,074 members
Please Sign up or sign in to vote.
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
Updated 24-Aug-21 1:52am

1 solution

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[^]
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900