0

I have a table like this:

enter image description here

How do i find latest value of each day in oracle with sql?

I try:

select max(datetime), amount
from ORDER_ITEMS
group by datetime, amount

But it does not give a right answer.

MT0
  • 143,790
  • 11
  • 59
  • 117
bibiji
  • 147
  • 1
  • 9

1 Answers1

0

You can use ROW_NUMBER() analytic function such as

SELECT datetime, amount
  FROM 
  ( SELECT datetime,
           amount,
           ROW_NUMBER() OVER(PARTITION BY TRUNC(datetime) ORDER BY datetime DESC) AS rn
      FROM order_items)
 WHERE rn = 1

if there might occur ties for datetime values, then prefer using DENSE_RANK() function instead as

SELECT datetime, amount
  FROM 
  ( SELECT datetime,
           amount,
           DENSE_RANK() OVER(PARTITION BY TRUNC(datetime) ORDER BY datetime DESC) AS dr
      FROM order_items)
 WHERE dr = 1
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thanks. How do i find the earliest amount for each day? – bibiji Feb 28 '22 at 07:30
  • you're welcome @bibiji, just remove the DESC after datetime column within the ORDER BY clause in order to determine the earliest data [such as](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=64e697248c145b15ad6daf187904551e) – Barbaros Özhan Feb 28 '22 at 08:19