How to Retrieve the Latest Quantity per ID and Timestamp in Oracle SQL?

Oracle SQL: Extracting the Most Recent Quantity for Each ID

This guide demonstrates how to efficiently retrieve the latest quantity for each unique ID from a table containing timestamped data in Oracle SQL.

The core approach involves a nested query strategy:

SELECT id, MAX(ts) AS "DATE", MAX(quantity) AS "QUANTITY"
FROM (
    SELECT id, ts, RANK() OVER (PARTITION BY id ORDER BY ts DESC) AS rnk, quantity
    FROM your_table_name
) ranked_data
WHERE rnk = 1
GROUP BY id;

This query operates as follows:

1. Inner Query (ranked_data): This subquery uses the RANK() window function to assign a rank to each row within each id group, ordered by the timestamp (ts) in descending order. The latest record for each id receives a rank of 1.

2. Outer Query: This query filters the results from the inner query, selecting only those rows with rnk = 1 (the latest records). It then groups the results by id and uses MAX() to retrieve the corresponding timestamp and quantity. Note that using MAX(quantity) with GROUP BY id is appropriate assuming that the latest timestamp also implies the latest quantity; if there could be multiple rows with the same latest timestamp but different quantities, a different aggregation method might be needed (like FIRST_VALUE in a more complex query).

Enhancements:

1. Time-Based Filtering: To restrict results to a specific time window (e.g., the last XX minutes), add a WHERE clause to the inner query:

WHERE ts >= SYSTIMESTAMP - INTERVAL 'XX' MINUTE

2. Joining with Another Table: To incorporate data from another table (e.g., an id_table with additional ID information), use a JOIN in the outer query:

SELECT id || '-' || idname AS "ID-IDNAME", MAX(ts) AS "DATE", MAX(quantity) AS "QUANTITY"
FROM (
    SELECT id, ts, RANK() OVER (PARTITION BY id ORDER BY ts DESC) AS rnk, quantity
    FROM your_table_name
) ranked_data
INNER JOIN id_table ON ranked_data.id = id_table.id
WHERE rnk = 1
GROUP BY id || '-' || idname;

Remember to replace your_table_name and id_table with your actual table names. This refined approach provides a robust and flexible solution for retrieving the most recent quantity data in Oracle SQL.

The above is the detailed content of How to Retrieve the Latest Quantity per ID and Timestamp in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!