sql - Return each max count of subgroup using oracle-11g -

i have table

create table table1 (    from_id varchar2(10),    to_id varchar2(10),    b_id varchar2(20) not null,    exp_in_date varchar2(20) not null ); 

sample data:

insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140203056',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140203056',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203056',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140203057',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('2','5','20140203057',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203057',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140203058',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('2','5','20140203058',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203058',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140203059',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('2','5','20140203059',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203059',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140204059',to_date('20140204', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140204059',to_date('20140204', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140204059',to_date('20140204', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140204059',to_date('20140204', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','2','20140204059',to_date('20140204', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203060',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203060',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203060',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203060',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','4','20140203061',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','3','20140203062',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','3','20140203062',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','3','20140203062',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','3','20140203062',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','3','20140203062',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203063',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203063',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203063',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203063',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203064',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203064',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203064',to_date('20140203', 'yyyymmdd')); insert table1 (from_id,to_id,b_id,exp_in_date) values ('5','1','20140203064',to_date('20140203', 'yyyymmdd')); 

i want max of count(*) per every '4' b_id, each 4 b_id represents transaction within hour, let's say

20140203056 20140203059

e.g. expected result

date-hr                     from_id to_id   count(no_record) ------------------------------------------------------------ february, 03 2014 14:00     2       5       1    february, 03 2014 14:00     5       1       1    

i can simple grouping. think can code in plsql, performance query important. if there no better solution, perform for-loop , if(greater) comparison.

do mean this?

select count(*) countx,from_id,to_id,exp_in_date table1 group from_id,to_id,exp_in_date; 

see fiddle.