I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance:

Each time a product is moved from position A to Position B, it is recorded (From_Function) / (To_Function) along with its operator and timestamp. On rare occasions, re-work is required, and that unit will show the same from / To instance with different timestamps.

I am currently calculating age in each position with a datediff, how do I return only the most recent in this scenario?

I got it down to if there is no group by clause it will return the date needed with a max(route_date_time); however, the total qry is many lines long with aggregate functions so a group by is necessary. How can I return only the one line that is needed with the most recent date?

closed as unclear what you're asking by ypercubeᵀᴹ, RolandoMySQLDBA, mustaccio, dezso, Shanky Jan 17 '16 at 5:19

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


Assuming an item moves between some type of state or location (From_Function, To_Function) and the timestamp of the move is in the route_date_time column, you could put your query in a common table expression, along with a ROW_NUMBER() function that calculates a sequence number, like so:

WITH cte AS (
    SELECT item_no,
           ToFunction AS current_function,
           ROW_NUMBER() OVER (
               PARTITION BY item_no
               ORDER BY route_date_time DESC) AS _rownum
    FROM some_complex_query

FROM cte
WHERE _rownum=1;

Because we've partitioned the ROW_NUMBER() function on item_no and ordered it by route_date_time descending, _rownum=1 will always return the most recent row for each item_no, which is what we're isolating using the WHERE clause at the end of the query.

If you need the time that each item has spent in a state, you could use LAG() (requires SQL Server 2012 or newer). Within the CTE, use the following expression:

             LAG(route_date_time, 1) OVER (
                 PARTITION BY item_no
                 ORDER BY route_date_time),
            ) AS time_spent_in_function

This will return the difference in number of seconds between the previous route_date_time (the LAG() function) and the current route_date_time. Bear in mind the PARTITION BY clause, which defines how you partition your records.

Not the answer you're looking for? Browse other questions tagged or ask your own question.