t, Sql Server - rextester

Language: Layout:

CREATE TABLE TimeTicketDet 
    ([TicketDate] varchar(9), [JobNo] int)
;    
INSERT INTO TimeTicketDet 
    ([TicketDate], [JobNo])
VALUES    
    ('5/9/2012', 20),
    ('5/10/2012', 20),
    ('5/24/2012', 20),
    ('3/27/2015', 20),
    ('5/22/2012', 20),
    ('5/10/2012', 20),
    ('5/11/2012', 20),
    ('5/9/2012', 100),
    ('5/10/2012', 100),
    ('5/24/2012', 100),
    ('3/27/2015', 100),
    ('5/22/2012', 100),
    ('5/10/2012', 100),
    ('5/11/2012', 100)
;

CREATE TABLE OrderDet 
    ([PartNo] int, [Status] varchar(4), [JobNo] int)
;
    
INSERT INTO OrderDet 
    ([PartNo], [Status], [JobNo])
VALUES
    (1234, 'Open', 20),
    (1234, 'Open', 100)
;

;WITH cte3 AS
(       
    SELECT
        o.PartNo, 
        o.JobNo,
        tt.TicketDate as rawr,
        ROW_NUMBER() OVER (PARTITION BY o.JobNo, o.PartNo 
                           ORDER BY cast(tt.TicketDate as datetime) DESC) as RowNum    
    FROM  OrderDet AS o    
    JOIN  TimeTicketDet AS tt 
      ON o.JobNo = tt.JobNo       
    WHERE
        o.Status = 'Open'
)    
SELECT *
FROM cte3
WHERE RowNum = 1

View schema
Execution time: 0,02 sec, rows selected: 2, rows affected: 16, absolute service time: 0,17 sec