Best approach: get the most recent N-side Record on a SQL Query on a N-1 relationship

My question is kinda simple... Let's suppose I have two simples tables on a 1-N relationship:

Order
----------
Id

Order_Status
--------------
Id
OrderId
Description
DateTimeStatus

I need to retrieve the following result: OrderId and the most recent Status's Description and Timestamp.

Usually I do things like this:

SELECT
    o.Id,
    st.Description,
    st.DateTimeStatus
FROM Order o
JOIN OrderStatus st ON
    st.OrderId = o.id
    AND
    st.DateTimeStatus = (
        SELECT MAX(st1.DateTimeStatus)
        FROM OrderStatus st1
        WHERE st1.OrderId = o.Id
    )

But I开发者_C百科 don't think it's the prettiest way, nor the most performatic one, thinking on more huge queries (and it's not safe without limiting the subquery result).

I could also simply join the two tables, ordering in descencing way the st.DateTimeStatus and limiting the result to 1.

Well.. Any better approach?

I researched for similar questions but haven't found something similar to what I want to know: the best approaches.

---

I could also simply join the two tables, ordering in descencing way the st.DateTimeStatus and limiting the result to 1.

I think you would be hard-pressed to find a more performant option than this.

---

You can use a CTE if you are using Microsoft SQL server 2005 or newer.

Something like this:

;with [Status] as (
select id,
    [description],
    [dateTimeStatus],
    ROW_NUMBER() over (PARTITION BY id ORDER BY dateTimeStatus desc) as row
    from OrderStatus st
    )
select
    id,
    [description]
    [dateTimeStatus]
from [Status]
where row = 1

You will have to check the Execution plan and Statistics to check if this would be the "BEST WAY" for you.

Sometimes the best way depends on what you need to accomplish and there are no silver bullets.