CODEWITHSUNDEEP
sqlsql-server-2008
jyoseph
jyoseph

Reputation: 5455

SQL Select latest records

I am trying to select a set of records from an orders table based on the latest status of that record. The status is kept in another table called orderStatus. My table is more complex, but here's a basic example

table - orders: 

orderID

table - orderStatus:

 orderStatusID
 orderID
 orderStatusCode
 dateTime

An order can have many status records, I simply want to get the orders that have the latest statusCode of what I'm querying for. Problem is I'm getting a lot of duplicates. Here's a basic example.

select orders.orderID
from   orders inner join orderStatus on orders.orderID = orderStatus.orderID
where  orderStatusCode = 'PENDING'

I've tried doing an inner query to select the top 1 from the orderStatus table ordered by dateTime. But I was still seeing the same duplication. Can someone point me in the right direction on how to go about doing this?

edit: SQL server 2008

Upvotes: 0

Views: 182

Answers (5)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

A simple LEFT JOIN to check that no newer status exists on an order should do it just fine;

SELECT o.* 
FROM orders o
JOIN orderStatus os
  ON o.orderID = os.orderID
LEFT JOIN orderStatus os2
  ON o.orderID = os2.orderID 
 AND os.dateTime < os2.dateTime
WHERE os.orderStatusCode = 'PENDING' AND os2.dateTime IS NULL;

Upvotes: 2

Sewder
Sewder

Reputation: 754

Try a subquery for the datetime?

 select orders.orderID
 from   orders inner join orderStatus on orders.orderID = orderStatus.orderID
 where  orderStatusCode = 'PENDING' AND (dateTime)=((SELECT MAX(dateTime) From orders 
 as orders2 Where orders= orders2))

Upvotes: 0

Wes Thurmond
Wes Thurmond

Reputation: 1

Try using a sub select like this:

Select
    o.*
FROM
    Order o inner join OrderStatus os on o.order_id = os.order_id
Where
    os.orderstatus_id in (SELECT max([orderstatus_id]) FROM [OrderStatus]  group by [order_id])
    And os.staus = 'pending'

Upvotes: 0

Sasidharan
Sasidharan

Reputation: 3750

select Distinct orders.orderID
from   orders inner join orderStatus on orders.orderID = orderStatus.orderID
where  orderStatusCode = 'PENDING'

Upvotes: 0

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

select DISTINCT orders.orderID
from   orders inner join orderStatus on orders.orderID = orderStatus.orderID
where  orderStatusCode = 'PENDING'

As an alternative your can GROUP BY

select orders.orderID
from   orders inner join orderStatus on orders.orderID = orderStatus.orderID
where  orderStatusCode = 'PENDING'
GROUP BY orders.orderID

Upvotes: 0

Related Questions