Select Latest Row for Each Group from Oracle

Select latest row for each group from oracle

You can use analytic functions

SELECT *
  FROM (SELECT c.*,
               rank() over (partition by user_id order by ts desc) rnk
          FROM comments c)
 WHERE rnk = 1

Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or dense_rank function rather than rank. rank would allow multiple rows to be first if there was a tie. row_number would arbitrarily return one row if there was a tie. dense_rank would behave like rank for the rows that tied for first but would consider the next row to be second rather than third assuming two rows tie for first.

Oracle SQL query: Retrieve latest values per group based on time

Given this data ...

SQL> select * from qtys
  2  /

        ID TS                      QTY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         1 2010-01-04 10:45        132
         2 2010-01-04 10:45        318
         4 2010-01-04 10:45        122
         1 2010-01-04 10:30          1
         3 2010-01-04 10:30        214
         2 2010-01-04 10:30       5515
         4 2010-01-04 10:30        210

9 rows selected.

SQL>

... the following query gives what you want ...

SQL> select x.id
  2         , x.ts as "DATE"
  3         , x.qty as "QUANTITY"
  4  from (
  5      select id
  6             , ts
  7             , rank () over (partition by id order by ts desc) as rnk
  8             , qty
  9      from qtys ) x
 10  where x.rnk = 1
 11  /

        ID DATE               QUANTITY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         3 2010-01-04 10:30        214
         4 2010-01-04 10:45        122

SQL>

With regards to your additional requirements, you can apply additional filters to the outer WHERE clause. Similarly you can join additional tables to the inline view like it was any other table.

Oracle query GROUP BY with latest record

I'd think of

• sorting rows by timestamp in descending order
• within the same client name

(see the ROW_NUMBER analytic function), and then fetch row(s) sorted as the first in that group.

Your result looks wrong for the 2nd group; 25th of April is "later" than 24th.

Sample data till line #8; actual query begins at line #10.

SQL> with reqresplog (reqts, clientname, itemid, outcome) as
  2  (select
  3   to_timestamp('2021-04-25 13:19:20:928', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD1', 'X0' from dual union all select
  4   to_timestamp('2021-04-24 13:20:22:345', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD2', 'Y0' from dual union all select
  5   to_timestamp('2021-04-26 13:21:35:456', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD3', 'X2' from dual union all select
  6   to_timestamp('2021-04-25 13:18:45:589', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.40.50', 'ABCD4', 'Y1' from dual union all select
  7   to_timestamp('2021-04-24 13:22:34:832', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.40.50', 'ABCD5', 'X0' from dual
  8  )
  9  select reqts, clientname, itemid, outcome
 10  from (select r.*,
 11          row_number() over (partition by r.clientname order by r.reqts desc) rn
 12        from reqresplog r
 13       )
 14  where rn = 1;

REQTS                          CLIENTNAME  ITEMID OUTCOME
------------------------------ ----------- ------ ----------
26.04.21 13:21:35,456000000    10.20.30.40 ABCD3  X2
25.04.21 13:18:45,589000000    10.20.40.50 ABCD4  Y1

SQL>

If, for some reason, you still want the 24th of April, change analytic function's parameters. How? No idea, I don't see anything obvious.

how to pull latest record for each ID from 2 tables in Oracle SQL

Try like this below:

select p.product_id, s.product_code, p.product_name, max(s.sold_date)
from product p
left join sold_product s on s.product_id = p.product_id
group by p.product_id, s.product_code, p.product_name

How to select latest value for each day in Oracle?

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

---

Related Topics