CODEWITHSUNDEEP
sql-server-2008t-sql
Agustin Meriles
Agustin Meriles

Reputation: 4854

select last record from a relation

I have this situation. I have a table Orders that is related to OrderStatus.

OrderStatus

id | orderId | created

I need to retrieve the Orders with its last status. I tried this query, what I don't know if it is performant. I need to know if there are better solutions.

select Orders.id, OrderStatus.status from Orders
inner join OrderStatus on OrderStatus.id = 
    (select top 1 id from OrderStatus where orderId = Order.id order by created desc)

Upvotes: 1

Views: 189

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use the Row_Number function:

WITH CTE AS
(
   SELECT Orders.id, OrderStatus.status,
      RN = ROW_NUMBER() OVER (
                        PARTITION BY OrderStatus.OrderId 
                        ORDER BY created DESC) 
   FROM Orders
   INNER JOIN OrderStatus ON  OrderStatus.OrderId = Orders.id
)
SELECT id, status
FROM CTE WHERE RN = 1

I've used a common-table-expression since it enables to filter directly and it's also very readable.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280423

Correlated subquery is usually bad news (sometimes SQL Server can optimize it away, sometimes it acts like a really slow loop). Also not sure why you think you need DISTINCT when you're only taking the latest status, unless you don't have any primary keys...

;WITH x AS 
(
  SELECT o.id, os.status, 
    rn = ROW_NUMBER() OVER (PARTITION BY os.orderId ORDER BY created DESC)
    FROM dbo.Orders AS o
    INNER JOIN dbo.OrderStatus AS os
    ON o.id = os.orderId
)
SELECT id, status
  FROM x
  WHERE rn = 1;

Upvotes: 2

Related Questions