Oracle: Getting highest value from two fields (date then value)

Question

I have something like this (date simplified to integer just for the example):

Order Date Value
12    5    555
12    5    800
12    2    900
13    3    122
13    4    155
14    1    121
...   ...  ...

And I'd like to get the order with the highest date and then the highest value:

Order Date Value
12    5    800
13    4    155
14    1    121
...   ...  ...

I know this is similar to several other questions but cant figure out how to apply those answers to my case, sorry.

Thanks!

@Seyran thanks, simple enough. But how do i get the "top value" of each Order then? — Gabriel, Apr 17 '17 at 15:12
If I understand you correct you want the `order ` value of row where ` date value` highest when should be like this, `select * from (....your select order by date desc, value desc ) where rownum = 1`. — Seyran, Apr 17 '17 at 15:16
@Seyran Almost there. I need to get the latest date and after that the highest value of *each* Order. The rownum = 1 should be applied at the Order level, somehow. — Gabriel, Apr 17 '17 at 15:25
What if you have 2 records with same values in `date` and `value` and they are highest but `order` is difference, is it possible ? — Seyran, Apr 17 '17 at 15:34
@Seyran yes, that's exactly the case. I need the max in date and value for each order. — Gabriel, Apr 17 '17 at 17:00
Ok, You have 2 nice answers from krokodilko and mathguy, hope your real columns don't have names you show in question sample. good luck. — Seyran, Apr 17 '17 at 18:07

score 2 · Answer 1 · answered Apr 17 '17 at 15:37

2

Use analytic function ROW_NUMBER

SELECT "Order", "Date", "Value"
FROM (
    select t.*, 
           row_number() 
               over (partition by "Order" order by "Date" desc, "Value" desc ) As rn
    FROM table1 t
) x
WHERE rn = 1

answered Apr 17 '17 at 15:37

krokodilko

35,300
7
55
79

score 2 · Answer 2 · answered Apr 17 '17 at 15:54

You can use the analytic FIRST/LAST function (see Oracle documentation). This solution does not need the subquery/outer query arrangement. I changed the column names since at least ORDER and DATE are Oracle keywords.

with
     inputs ( ord, dt, val ) as (
       select 12, 5, 555 from dual union all
       select 12, 5, 800 from dual union all
       select 12, 2, 900 from dual union all
       select 13, 3, 122 from dual union all
       select 13, 4, 155 from dual union all
       select 14, 1, 121 from dual
     )
--  End of test data (not part of the solution).
--  SQL query begins BELOW THIS LINE
select   ord, max(dt) as dt, max(val) keep (dense_rank last order by dt) as val
from     inputs
group by ord
;

ORD  DT  VAL
---  --  ---
 12   5  800
 13   4  155
 14   1  121

Nice approach ... worthwhile taking a look at [RANK, DENSE_RANK, FIRST and LAST](https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions#dense_rank) ... I am voting up ;-) — PKey, Apr 17 '17 at 16:07

PKey · Answer 3 · 2017-04-17T15:50:49.377

Lets assume your table name is orders

In a subquery you find orders with highest dates and for those you find highest values... It will look like this:

   select order,date,max(value) value from orders
           where (order,date) in 
                           (select order, max(date) date
                            from orders
                            group by order
                           )
           group by order,date
           order by order,date desc

Oracle: Getting highest value from two fields (date then value)

3 Answers3