By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

How to select the duplicate value if both are in the same interval

P: 2
Hi,

can anyone help me to figure this out.
Currently, i have this code:
Expand|Select|Wrap|Line Numbers
  1. select 
  2.   t_stamp, 
  3.   num 
  4. from table
  5. where num in 
  6.    (select num from click_log 
  7.     where t_stamp between '2010-07-21 12:00:00' 
  8.     and '2010-07-21 13:00:00'
  9.     group by num 
  10.     having count(*) > 1)
  11. order by num;
  12.  
this code will get the duplicate values within interval of 1 hour. This is not the problem. the problem is how to get duplicate values for every 15 mins of time range (0-15,15-30,30-45,45-60) within interval of 1 hour.

lets say if my records look like this:
Expand|Select|Wrap|Line Numbers
  1. tstamp              | num
  2. --------------------+-----
  3. 2010-07-21 11:59:05 |  a
  4. 2010-07-21 12:05:23 |  a
  5. 2010-07-21 12:08:47 |  c
  6. 2010-07-21 12:11:30 |  a
  7. 2010-07-21 12:18:11 |  a
  8. 2010-07-21 12:24:09 |  b
  9. 2010-07-21 12:33:41 |  a
  10. 2010-07-21 12:35:29 |  b
  11. 2010-07-21 12:39:09 |  b
  12. 2010-07-21 12:43:51 |  a
  13. 2010-07-21 12:45:17 |  a
  14. 2010-07-21 12:49:05 |  c
  15. 2010-07-21 12:56:00 |  c
  16. 2010-07-21 13:10:32 |  a
  17.  
  18. the output should be look like this:
  19.  
  20. tstamp              | num
  21. --------------------+-----
  22. 2010-07-21 12:05:23 |  a    } in 0-15mins
  23. 2010-07-21 12:11:30 |  a    }
  24. _________________________
  25. 2010-07-21 12:33:41 |  a    } in 30-45mins
  26. 2010-07-21 12:35:29 |  b    }
  27. 2010-07-21 12:39:09 |  b    }
  28. 2010-07-21 12:43:51 |  a    }
  29. _________________________
  30. 2010-07-21 12:49:05 |  c    } in 45-60mins
  31. 2010-07-21 12:56:00 |  c    }
  32.  
I appreciate any help.

thanks :)
Jul 30 '10 #1
Share this Question
Share on Google+
2 Replies


P: 2
fyi, i'm using postgres 8.2 :)
Jul 30 '10 #2

Expert 100+
P: 700
All ranges in one query? Can you use UNION clauses?
Aug 3 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.