Results 1 to 1 of 1

Thread: sql query help

  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: sql query help

    i have a table answer with the following fields: id, timestamp, and flag(0 or 1). the table just keeps track of the answer of id whether it's correct(flag=1) or wrong(flag=0), timestamp is the time when the answer is sent.

    i need an sql statement to find out the speed of each id in getting who got say 3 correct answers (same day, the number of correct answer is dynamic). i.e. from the data below:

    id | timestamp | flag
    ----+---------------------+------
    1 | 2004-04-23 00:01:00 | 1
    1 | 2004-04-23 00:02:00 | 1
    1 | 2004-04-23 00:03:00 | 0
    1 | 2004-04-23 00:04:00 | 1
    1 | 2004-04-23 00:04:30 | 1
    1 | 2004-04-23 00:05:00 | 1
    2 | 2004-04-23 00:01:03 | 1
    2 | 2004-04-23 00:02:01 | 1
    2 | 2004-04-23 00:02:21 | 0
    2 | 2004-04-23 00:02:34 | 1
    3 | 2004-04-23 00:03:33 | 1
    3 | 2004-04-23 00:03:35 | 1
    3 | 2004-04-23 00:03:38 | 1

    the query should return this (set number of correct answer to 3, ordered by speed):
    id | speed | start_time | end_time
    ----+----------+---------------------+---------------------
    3 | 00:00:05 | 2004-04-23 00:03:33 | 2004-04-23 00:03:38
    1 | 00:01:00 | 2004-04-23 00:04:00 | 2004-04-23 00:05:00
    2 | 00:01:31 | 2004-04-23 00:01:03 | 2004-04-23 00:02:34
    1 | 00:02:30 | 2004-04-23 00:02:00 | 2004-04-23 00:04:30
    1 | 00:03:00 | 2004-04-23 00:01:00 | 2004-04-23 00:04:00

    end_time would be the timestamp of the 3rd correct answer. speed = end_time - start_time.

    any ideas how to formulate the query? any help will be appreciated. thanks.
    Last edited by esi; 06-04-04 at 06:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •