SQL: Get latest record

Question

Hi all,

this is my relational model:

  Request
  ------------------------------
  RequestId
  ------------------------------
  1
  2


  RequestState
  ------------------------------
  RequestStateId | Name
  ------------------------------
  10             | Received
  20             | Processing
  30             | Finsihed


  Request_RequestState
  -------------------------------------------------------------------
  Request_RequestStateId | RequestId | RequestStateId | CreatedOn
  -------------------------------------------------------------------
  1                      | 1         | 10             | 2010-01-01
  2                      | 1         | 20             | 2010-01-02
  3                      | 2         | 10             | 2010-01-15

Each time a request state changes, this change is stored. Now I need to list requests by its current state. Like "Get all requests with current state = Received".

So far I only managed to created a query that return requests of a given state, but it doesn't matter if it is the current state or an older one... So I somehow need to use CreatedOn to get the latest/current state.

Any help? Thanks in advance!

Answer 1

This should do it for you:

SELECT      r.RequestId,
            s.Name AS RequestStateName
FROM        Request r
INNER JOIN  Request_RequestState rs
        ON  rs.Request_RequestStateId = (
                SELECT  TOP 1 x.Request_RequestStateId
                FROM        Request_RequestState x
                WHERE       x.RequestId = r.RequestId
                        --// you could add filter to get "current" status at some DATE
                        --//AND x.CreatedOn < '2010-01-15'
                ORDER BY    x.CreatedOn DESC
            )                    
INNER JOIN  RequestState s
        ON  s.RequestStateId = rs.RequestStateId 

WHERE       s.Name = 'Received'

You can also get all "current" request as of some other date, if you use filter as commented in the code.

I would probably just create a view from the SQL query above, and use it:

SELECT * FROM MyRequestStateView WHERE RequestStateName = 'Received'

Answer 2

Assuming that Request_RequestStateId increments up with time (i.e. the records with the greatest ID has the latest CreatedOn date)....

SELECT rrs.RequestId, rrs.RequestStateId, rs.Name
FROM Request_RequestState rrs
    JOIN (
        SELECT MAX(Request_RequestStateId) AS LatestRequestStateId
        FROM Request_RequestState
        GROUP BY RequestId
    ) rrs2 ON rrs.Request_RequestStateId = rrs2.LatestRequestStateId
    JOIN RequestState rs ON rrs.RequestStateId = rs.RequestStateId
WHERE rs.Name = 'Received'

Answer 3

A possible query could look like that:

select r.requestId,
rs.RequestStateId,
rs.Name,
rrs.CreatedOn
from (select r2.* from request_requeststate where r2.createdon = (select max(createdon) from request_requeststate r3 where r3.request_requeststateId = r2.request_requeststateId)) rrs
inner join requeststate rs on rs.requeststateId = rrs.reqeststateid
inner join request r on r.requestid = rrs.requestid

You could use this query as a view or add a where clause where you filter for a specific request-state.

Answer 4

You change your model...

With the current scheme, as more and more data changes take place, it will take longer and longer to determine the current state using the queries suggested above...

You need a "Current_Request_State" attribute on your request.

Answer 5

This query should also give you what you want but I also agree with Martin Milan that you should consider caching the most recent status value on the Request table.

SELECT r.RequestId, rrs.RequestStateId, rs.RequestStateName, rrs.StateChangedDate
FROM Request r
    INNER JOIN (
        SELECT ROW_NUMBER() OVER (PARTITION BY RequestId ORDER BY CreatedOn DESC) AS ROWNUM,
                RequestId,
                RequestStateId
                CreatedOn
        FROM Request_RequestState       
    ) rrs 
        ON r.RequestId = rrs.RequestId 
        AND ROWNUM = 1
    INNER JOIN RequestState rs 
        ON rrs.RequestStateId = rs.REquestStateId