Parents Whose Last Created Child is a 'Failure'

Can you try out like this where Subquery has been replaced by INNER JOIN:

SELECT Parent__c, Parent__r.Name, COUNT(Id), MAX (CreatedDate)
FROM Child__c 
WHERE Status__c = 'Failure' AND Parent__r.RecordType.Name = 'rt'
GROUP BY Parent__c,Parent__r.Name
ORDER BY Parent__c, Parent__r.Name LIMIT 100

I have tested this at my org.

It will show latest Child created date with status = 'Failure'. Also, for a parent single Child record will be filtered. For this reason, COUNT(Id) and Max(CreatedDate) comes into play.

As Adrian noted in the comments on your question, this isn't possible to achieve via SOQL alone.

While you can't use ORDER BY in a semi-join/Left Inner Join (i.e. a subquery appearing in the WHERE), you can use ORDER BY in a Left Outer Join (a.k.a. a parent-child subquery, one that appears in the SELECT clause).

I can't find documentation that corroborates that last statement, but I have the next best thing, code that anyone can run to prove it for themselves. If you feel the need, just run the following as anonymous apex:

for(Account a :[SELECT Id, (SELECT Id FROM Opportunities ORDER BY CreatedDate ASC LIMIT 1) FROM Account LIMIT 10]){
    system.debug(a.Id + ': ' + a.Opportunities);
}

Given that the above works, an approach that I might suggest would be to use a Left Outer Join to find the 'last created child' (which I take to mean the most recently created child), and then use apex to figure out whether or not that child record's status is 'Failure'

Set<Id> latestChildFailureParentIds = new Set<Id>();

// If you want to find the oldest child record, simply replace 'ORDER BY CreatedDate DESC'
//   with 'ORDER BY CreatedDate ASC'
for(Parent p :[SELECT Id, Name, (SELECT Id, Status FROM Children ORDER BY CreatedDate DESC LIMIT 1) FROM Parent WHERE recordType.Name = 'rt']){
    if(!p.Children.isEmpty() && p.Children[0].Status == 'Failure'){
        latestChildFailureParentIds.add(p.Id);
    }
}

It doesn't help you with historical data, but I would set up a flow using Process Builder to update a Most Recent Child Status field (or a lookup to that child record). Make sure to have it fire only on record creation.

Once it's populated, just filter on this field. For example, if you use a relationship, include this clause:

WHERE Most_Recent_Child__r.Status__c = 'Failure'

A batch to backfill wouldn't be too much work, for the historical aspect.

Query (start)

SELECT (
    SELECT Status__c FROM Children__r
    ORDER BY CreatedDate DESC LIMIT 1
) FROM Parent__c WHERE Id IN (
    SELECT Parent__c FROM Child __c
)

Logic (execute)

for (Parent__c parent : scope) 
    parent.Most_Recent_Child__c = parent.Children__r[0].Id;
update scope;