How to Retrieve the Latest Values Per Group in Oracle SQL Based on Time?

Using Oracle SQL to Extract the Latest Data Points per Group Based on Timestamp

This guide demonstrates several methods for retrieving the most recent values for each group in an Oracle database, ordered by a timestamp.

Fundamental Query:

The following query retrieves the latest value for each id, along with its associated date and quantity:

<code class="language-sql">SELECT id, MAX(date) AS "DATE", MAX(quantity) AS "QUANTITY"
FROM qtys
GROUP BY id;</code>

Method 1: Filtering by a Specific Time Window (e.g., Last XX Minutes)

To restrict the results to entries within the last XX minutes, incorporate a WHERE clause:

<code class="language-sql">SELECT id, date, quantity
FROM qtys
WHERE date > (SELECT MAX(date) - INTERVAL 'XX' MINUTE FROM qtys);</code>

Method 2: Combining IDs with Textual Data from Another Table

To append IDs with corresponding text from a table named "idnames," utilize the following query:

<code class="language-sql">SELECT t1.id || '-' || t2.idname AS "ID-IDNAME",
       MAX(t1.date) AS "DATE",
       MAX(t1.quantity) AS "QUANTITY"
FROM qtys t1
INNER JOIN idnames t2 ON t1.id = t2.id
GROUP BY t1.id;</code>

Illustrative Results:

The expected output format resembles this:

<code>ID-IDNAME  DATE                QUANTITY
---------- ------------------- ----------
1-testid1  2010-01-04 11:00        152
2-testid2  2010-01-04 11:00        210
3-testid3  2010-01-04 10:30        214
4-testid4  2010-01-04 10:45        122</code>

The above is the detailed content of How to Retrieve the Latest Values Per Group in Oracle SQL Based on Time?. For more information, please follow other related articles on the PHP Chinese website!