select t.*,t.rownum from tablename t where time >= '2011-12-09 07%' and time <= '2011-12-09 08%' and t.rownum<=10 UNION ALL select t.*,t.rownum from tablename t where time >= '2011-12-09 09%' and time <= '2011-12-09 10%' and t.rownum<=10 UNION ALL select t.*,t.rownum from tablename t where time >= '2011-12-09 11%' and time <= '2011-12-09 12%' and t.rownum<=10
select * from ( select row_number() over (partition by trunc(time_data,'hh') order by time_data) rn,t.* from t where sign(time_data-to_date('2009-12-09 07','yyyy-mm-dd hh24'))+sign(time_data-to_date('2009-12-09 08','yyyy-mm-dd hh24'))=0 or sign(time_data-to_date('2009-12-09 09','yyyy-mm-dd hh24'))+sign(time_data-to_date('2009-12-09 10','yyyy-mm-dd hh24'))=0 or ...)t2 where t2.rn <=10
UNION ALL
select t.*,t.rownum from tablename t where time >= '2011-12-09 09%' and time <= '2011-12-09 10%' and t.rownum<=10
UNION ALL
select t.*,t.rownum from tablename t where time >= '2011-12-09 11%' and time <= '2011-12-09 12%' and t.rownum<=10
select row_number() over (partition by trunc(time_data,'hh') order by time_data) rn,t.* from t
where sign(time_data-to_date('2009-12-09 07','yyyy-mm-dd hh24'))+sign(time_data-to_date('2009-12-09 08','yyyy-mm-dd hh24'))=0 or sign(time_data-to_date('2009-12-09 09','yyyy-mm-dd hh24'))+sign(time_data-to_date('2009-12-09 10','yyyy-mm-dd hh24'))=0 or ...)t2
where t2.rn <=10