create table test
(
id int ,
ip_id varchar(5),
max_val int
)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (1,'1001',3)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (2,'1001',5)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (3,'1001',9)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (4,'1003',4)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (5,'1007',8)
INSERT INTO KITE.TEST (ID,IP_ID,MAX_VAL) VALUES (6,'2001',2)
select * from test as a1
where exists
(
select 1 from
(
select ip_id,max(max_val) as max_val from test group by ip_id
) a2
where a1.ip_id=a2.ip_id and a1.max_val=a2.max_val
)