Search code examples
sqloracleoracle-sqldeveloper

How to get rows without single max value row


I have table in Oracle SQL. Table contains different values for different objects and I need to select every record except one with max value grouped by objects, but max value can have duplicate record in the table below. I need to use analytical functions.

object value
obj1   100
obj1   100
obj1   50
obj1   50
obj2   30
obj2   10
obj2   10

So I need all records except single record with max value for each group. For example above my SQL query result must be

object value
obj1   100
obj1   50
obj1   50
obj2   10
obj2   10

As you can see, result contains all records except two records that have max value per object, but result contains duplicate records of max value.

I tried something like this

SELECT * 
FROM (SELECT object, value, max(value) OVER (PARTITION BY object) as maxval FROM table)
WHERE value != maxval

But this query returns duplicates of records with max value. Is there any solution with as less subqueries as possible?


Solution

  • So I need all records except single record with max value for each group.

    You can use window functions:

    select object, value
    from (select t.*,
                 row_number() over (partition by object, value order by value) as seqnum_ov,
                 max(value) over (partition by object) as max_value
          from t
         ) t
    where max_value <> value or seqnum_ov > 1