How to Retrieve the Latest Quantity for Each ID 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 within an Oracle SQL database. Let's assume you have a table called "qtys" with the following structure:

• ID: Unique identifier for each record.
• TS: Timestamp indicating when the quantity was recorded.
• QTY: The quantity value.

The goal is to obtain the most recent quantity (QTY) and its corresponding timestamp (TS) for every distinct ID.

Solution:

The following query utilizes the MAX() function and GROUP BY clause to achieve this:

<code class="language-sql">SELECT
    id,
    MAX(TS) AS "Latest Timestamp",
    MAX(QTY) KEEP (DENSE_RANK LAST ORDER BY TS) AS "Latest Quantity"
FROM
    qtys
GROUP BY
    id;</code>

This query groups the data by ID and then, for each group, selects the maximum timestamp (MAX(TS)). Crucially, MAX(QTY) KEEP (DENSE_RANK LAST ORDER BY TS) ensures that the quantity associated with the latest timestamp is retrieved, even if multiple entries share the same maximum timestamp.

Alternative Approaches:

1. Filtering within the Last X Minutes:

To limit results to entries within the last X minutes, incorporate a WHERE clause:

<code class="language-sql">SELECT
    id,
    MAX(TS) AS "Latest Timestamp",
    MAX(QTY) KEEP (DENSE_RANK LAST ORDER BY TS) AS "Latest Quantity"
FROM
    qtys
WHERE
    TS > SYSTIMESTAMP - INTERVAL '30' MINUTE
GROUP BY
    id;</code>

Replace '30' MINUTE with your desired time interval.

2. Joining with Another Table:

If you need to incorporate data from another table (e.g., "names" with a column idname), use a JOIN:

<code class="language-sql">SELECT
    t1.id || '-' || t2.idname AS "ID-IDNAME",
    MAX(t1.TS) AS "Latest Timestamp",
    MAX(t1.QTY) KEEP (DENSE_RANK LAST ORDER BY t1.TS) AS "Latest Quantity"
FROM
    qtys t1
JOIN
    names t2 ON t1.id = t2.id
GROUP BY
    t1.id || '-' || t2.idname;</code>

This joins "qtys" and "names" based on the id column and concatenates the ID and idname for a combined identifier. The MAX() functions then operate on the joined data. Remember to adjust table and column names to match your specific database schema.

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