How to Retrieve the Latest Value for Each Group Based on Timestamp in Oracle?

Retrieve the latest value of each group based on time

Question:

Consider the following Oracle table:

<code>id     date              quantity
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</code>

The goal is to retrieve the latest value (and its timestamp) by id group. Expected output should be:

<code>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</code>

Solution:

To do this, create an inline view that ranks each row in the id group based on descending timestamp:

<code class="language-sql">SELECT RANK() OVER (PARTITION BY id ORDER BY ts DESC) AS rnk,
       id, ts, qty
FROM qtys</code>

Then filter the record with rank 1 to get the latest value:

<code class="language-sql">SELECT x.id, x.ts AS "DATE", x.qty AS "QUANTITY"
FROM (SELECT * FROM qtys) x
WHERE x.rnk = 1</code>

Advanced options:

• Filter by time: To retrieve only values ​​within the last XX minutes, add the following condition to the WHERE clause:

<code class="language-sql">WHERE x.ts >= sysdate - INTERVAL 'XX' MINUTE</code>

• Join id with data from another table: If the idname column exists in another table, join those tables to join the values:

<code class="language-sql">SELECT x.id || '-' || y.idname AS "ID",
       x.ts AS "DATE", x.qty AS "QUANTITY"
FROM (SELECT * FROM qtys) x
LEFT JOIN another_table y ON x.id = y.id</code>

The above is the detailed content of How to Retrieve the Latest Value for Each Group Based on Timestamp in Oracle?. For more information, please follow other related articles on the PHP Chinese website!