CODEWITHSUNDEEP
sqljoinsql-server-2008-r2
Simon
Simon

Reputation: 9425

SQL Join to get most recent record

I have three tables:

I need to get for every measurement, the most recent event (in the past) and its associated eventvalues data.

My current query is quite ugly:

SELECT 
    M.*,
    (SELECT TOP 1 EV.value FROM [Event] E JOIN EventValues EV ON E.EventID = EV.EventID 
     WHERE M.Time >= E.Time ORDER BY M.Time-E.Time) AS Data,
FROM [Measure] M 
ORDER BY M.Distance

and it only allows me to select one column from the EventValues table (I need more)

Is there any way this can be done using a join?

EDIT: I also need to select ALL entries from the measurement table even if they are before the first event (i.e. just select null data for the join)

Upvotes: 2

Views: 1646

Answers (2)

amit_g
amit_g

Reputation: 31250

Try something like this (not tested)

SELECT * FROM
(
    SELECT M.*, E.*, EV.EventDataID, EV.Type, EV.Value,
        Rank() over (Partition BY M.MeasureID order by M.Time - E.Time) as Rank
    FROM [Measure] M 
    INNER JOIN [Event] E ON M.Time >= E.Time
    INNER JOIN EventValues EV ON E.EventID = EV.EventID
) T
WHERE Rank = 1

EDIT

SELECT * FROM
(
    SELECT M.*, E.*, EV.EventDataID, EV.Type, EV.Value,
        Rank() over (Partition BY M.MeasureID order by M.Time - E.Time) as Rank
    FROM [Measure] M 
    LEFT JOIN [Event] E ON M.Time >= E.Time
    LEFT JOIN EventValues EV ON E.EventID = EV.EventID
) T
WHERE Rank = 1

Upvotes: 1

GilM
GilM

Reputation: 3761

You can use CROSS APPLY.

SELECT  
    M.*, Data.* 
FROM [Measure] M 
CROSS APPLY
     (SELECT TOP 1 EV.* FROM [Event] E JOIN EventValues EV ON E.EventID = EV.EventID  
     WHERE M.Time >= E.Time ORDER BY E.Time DESC) AS Data
ORDER BY M.Distance

Upvotes: 1

Related Questions