Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Select records at interval of 5 minutes

645384Jun 17 2008 — edited Apr 24 2010
hi, I've asked this question before, but it's not solved yet. Thanks a lot for all your suggestion and help!

The data is like this:


TYPE | ID | TIME
CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
CHIT|1000311370|11-JUN-08 05.40.10.000000000 AM
CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
CHIT|9050|11-JUN-08 05.49.41.000000000 AM
CHIT|9050|11-JUN-08 05.50.41.000000000 AM
CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
CMIK|5480|11-JUN-08 05.54.30.000000000 AM
CHIT|1000005980|11-JUN-08 05.54.36.000000000 AM
CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
CMIK|5480|11-JUN-08 06.01.03.000000000 AM
CMIK|1000094670|11-JUN-08 06.01.03.000000000 AM
CMIK|1000095130|11-JUN-08 06.04.42.000000000 AM
CMIK|5480|11-JUN-08 06.04.42.000000000 AM
CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
CMIK|9040|11-JUN-08 06.25.44.000000000 AM
CHIT|1000311390|11-JUN-08 06.25.50.000000000 AM
CHIT|5480|11-JUN-08 06.25.50.000000000 AM
CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
CMIK|5480|11-JUN-08 06.51.42.000000000 AM
CMIK|1000251580|11-JUN-08 06.51.42.000000000 AM
CMIK|5480|11-JUN-08 06.58.12.000000000 AM
CMIK|1000251580|11-JUN-08 06.58.12.000000000 AM
EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
EHUY|100663396|11-JUN-08 07.09.50.000000000 AM
EHUY|100663418|11-JUN-08 07.09.50.000000000 AM
CMIK|5480|11-JUN-08 07.27.27.000000000 AM
CMIK|1000094670|11-JUN-08 07.27.27.000000000 AM
CMIK|1000289540|11-JUN-08 07.30.28.000000000 AM

The requirement:
Starting from the 1st record, skip for 5 minutes (or more, if we could not find one record at exact 5 mintutes point), then choose the next one; then skip for another 5 minutes (or more) and so on. Putting it in another way, we'll ignore records within 5 minutes time range.

So, the result from the above data should be:

TYPE | ID | TIME
CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
CMIK|5480|11-JUN-08 06.58.12.000000000 AM
CMIK|5480|11-JUN-08 07.27.27.000000000 AM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2008
Added on Jun 17 2008
22 comments
26,996 views