Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
27,345 views