can anyone help me to figure this out.
Currently, i have this code:
Expand|Select|Wrap|Line Numbers
- select
- t_stamp,
- num
- from table
- where num in
- (select num from click_log
- where t_stamp between '2010-07-21 12:00:00'
- and '2010-07-21 13:00:00'
- group by num
- having count(*) > 1)
- order by num;
lets say if my records look like this:
Expand|Select|Wrap|Line Numbers
- tstamp | num
- --------------------+-----
- 2010-07-21 11:59:05 | a
- 2010-07-21 12:05:23 | a
- 2010-07-21 12:08:47 | c
- 2010-07-21 12:11:30 | a
- 2010-07-21 12:18:11 | a
- 2010-07-21 12:24:09 | b
- 2010-07-21 12:33:41 | a
- 2010-07-21 12:35:29 | b
- 2010-07-21 12:39:09 | b
- 2010-07-21 12:43:51 | a
- 2010-07-21 12:45:17 | a
- 2010-07-21 12:49:05 | c
- 2010-07-21 12:56:00 | c
- 2010-07-21 13:10:32 | a
- the output should be look like this:
- tstamp | num
- --------------------+-----
- 2010-07-21 12:05:23 | a } in 0-15mins
- 2010-07-21 12:11:30 | a }
- _________________________
- 2010-07-21 12:33:41 | a } in 30-45mins
- 2010-07-21 12:35:29 | b }
- 2010-07-21 12:39:09 | b }
- 2010-07-21 12:43:51 | a }
- _________________________
- 2010-07-21 12:49:05 | c } in 45-60mins
- 2010-07-21 12:56:00 | c }
thanks :)