CODEWITHSUNDEEP
sql-servert-sqljoinsubquery
MadBojo
MadBojo

Reputation: 13

Query for item with subset of related items

I've got two tables: 

Part (Table)  
----  
PartID
SerialNumber
CreationDate

Test (Table)
----
PartID
TestName
TestDateTime
TestResult

The tables have a one to many relationship on PartID, one part may have many Test entries.

What I'm trying to do is return a list of parts with the information of only the last test performed on that part.

Part                                 Test
PartID  SerialNumber CreationDate    PartID TestName TestDateTime TestResult
--------------------------------     -------------------------------------------
1       555          12/9/2013       1      Test 1   1/1/2014     Pass
                                     1      Test 2   2/2/2014     Fail

I would like to return the last test data with the part's information:

PartID SerialNumber CreationDate TestName TestDateTime TestResult
-----------------------------------------------------------------
1      555          12/9/2013    Test 2   2/2/2014     Fail

I can currently get the TestDateTime of the part's last test, but no other information with this query (as a subquery cannot return more than more item):

SELECT PartID, SerialNumber, CreationDate,
       (SELECT        TOP (1) TestDateTime
        FROM            Test
        WHERE        (PartID = Part.PartID)
        ORDER BY TestDateTime DESC) AS LastDateTime
FROM            Part
ORDER BY SerialNumber

Is there a different approach I can take to get the data I'm looking for?

Upvotes: 1

Views: 101

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Here is another way to do that only hits the Test table one time.

with SortedData as
(
    SELECT PartID
        , SerialNumber
        , CreationDate
        , TestDateTime
        , ROW_NUMBER() over (Partition by PartID ORDER BY TestDateTime DESC) AS RowNum
    FROM Part p
    join Test t on t.PartID = p.PartID
)

select PartID
    , SerialNumber
    , CreationDate
    , TestDateTime
from SortedData
where RowNum = 1
ORDER BY SerialNumber

If you are on 2012 or later you can also use FIRST_VALUE

Upvotes: 2

KHeaney
KHeaney

Reputation: 785

Try using a sub query in your join and then filter based on that. Your Sub query should select the PardID and Max(TestDateTime)

Select TestSubQ.PartID, Max(TestSubQ.TestDateTime)
From Test TestSubQ
group by TestSubQ.PartID

Then just filter your main query by joining this table

Select Part.PartID, SerialNumber, CreationDate,
       TestMain.PartID, TestMain.TestName, TestMain.TestDateTime, TestMain.TestResult
From Part
    Left Outer Join (Select TestSubQ.PartID, Max(TestSubQ.TestDateTime)
                     From Test TestSubQ
                     group by TestSubQ.PartID) TestPartSub
        On Part.PartID = TestPartSub.PartID
    Left Outer Join Test TestMain
        On TestPartSub.PartID = TestMain.PartID
           And TestPartSub.TestDateTime = TestMain.TestDateTime
Order By SerialNumber

Note though that if your data only contains dates and not times then you may still end up with 2 entries if two tests were done on the same date. If time is included though it is highly unlikely that two exact datetimes will match for two different tests for any one part.

Upvotes: 0

Related Questions