Oracle 11g – Exclude records from set where the hour column is greater than

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.

Answer

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;

Advertisement

Answer