Ufdjrw

Consider the following, minimal example:

SQL> select key1, key2, key3, hour from teste_jm;

 KEY1 KEY2 KEY3 HOUR
---------- ---------- ---------- -----
 26 992296089 1504 12:30
 26 992296089 1275 13:30

I'd like to exclude the record where KEY3 is 1275, because its HOUR is greater than that of the record where KEY3 is 1504.

I tried the following, but to no success (I expected KEY4 to be 1504):

SQL> select key1, key2, key3, hour, min(key3) over (partition by key1, key2, key3 order by to_date(hour, 'HH24:MI')) key4 from teste_jm;

 KEY1 KEY2 KEY3 HOUR KEY4
---------- ---------- ---------- ----- ----------
 26 992296089 1275 13:30 1275
 26 992296089 1504 12:30 1504

So essentially, what I'd like to do, in Oracle 11g, is to keep only the records whose hour is the earliest, for the same KEY1, KEY2 pair.

How would I do this?

EDIT: Here's a more complete example:

SQL> select * from teste_jm;

 KEY1 KEY2 KEY3 HOUR
---------- ---------- ---------- -----
 27 993334444 1 23:00
 27 993334444 2 23:59
 26 992296089 1504 12:30
 26 992296089 1275 13:30

Desired output for the above:

 KEY1 KEY2 KEY3 HOUR
---------- ---------- ---------- -----
 27 993334444 1 23:00
 26 992296089 1504 12:30

SQLFiddle containing the sample data here.

You are close but you need a subquery to filter and the right logic for the minimum:

select key1, key2, key3, hour
from (select key1, key2, key3, hour,
 min(to_date(hour, 'HH24:MI')) over (partition by key1) as min_hour
 from teste_jm
 ) t
where hour = min_hour;

Why dont you just group by and get the min and I hope the above input vs o/p sample is correct so as to sync with below query

 Select KEY1, KEY2 ,min(KEY3), HOUR 
 from table group by
 KEY1, KEY2
 having HOUR=min(HOUR)

try to do this

select key1, key2, key3, hour from teste_jm where hour = (select min(hour) from teste_jm);

Uh, after your edit, Try this

select key1, key2, key3, min(hour) OVER (PARTITION BY key1,key2) from teste_jm;