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?
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