select the 2 earliest date records for each id

[lbrannon]

I need a query to select the 2 earliest date records for each id in the following table and then to find the id's where the difference between those dates is greater than 5 days. I think I've got the DateDiff part down I just can't seem narrow down my dates to the first 2 for each id. Any ideas?

WorkRequestID	ComponentClass	InspectionDate	InspectionHistoryID	NetReading	PassFail
15567	VALVE	1/5/2015	1446829	49485	FALSE
15567	VALVE	1/9/2015	1451761	4	TRUE
15568	VALVE	1/5/2015	1446890	6903	FALSE
15568	VALVE	1/9/2015	1451762	1871	FALSE
15568	VALVE	1/15/2015	1455215	4	TRUE
15569	VALVE	1/5/2015	1446916	2540	FALSE
15569	VALVE	1/9/2015	1451763	10	TRUE
15570	VALVE	1/6/2015	1447819	534	FALSE
15570	VALVE	1/8/2015	1450796	182	TRUE
15571	VALVE	1/6/2015	1448730	515	FALSE
15571	VALVE	1/9/2015	1451627	597	FALSE
15571	VALVE	1/21/2015	1459640	2286	FALSE
15571	VALVE	1/21/2015	1461034	2282	FALSE
15571	VALVE	2/5/2015	1472222	0	TRUE
15571	VALVE	3/17/2015	1499486	0	TRUE

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

[lbrannon]

So, I'm having a problem with it selecting the 2 newest dates instead of the oldest. It doesn't matter what order I have the table in. Is there a way to get it to select the 2 oldest dates?

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate
FROM tblWorkReqInsp
WHERE (((tblWorkReqInsp.InspectionDate) In (SELECT TOP 2 InspectionDate                            
   FROM tblWorkReqInsp AS Dupe                              
   WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID        
   ORDER BY Dupe.InspectionDate DESC, Dupe.WorkRequestID DESC)))
ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[June7]

The selection should be on a unique ID field. Dates are not unique. An autonumber field can serve.

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate
FROM tblWorkReqInsp
WHERE ID In (SELECT TOP 2 ID
FROM tblWorkReqInsp AS Dupe
WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID
ORDER BY Dupe.InspectionDate DESC, Dupe.WorkRequestID DESC)
ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[lbrannon]

This is still giving me the same problem. I tried sorting and saving the table several different ways before adding the autonumber ID field, but it doesn't seem to matter. It always autonumbers it the same way... with the oldest date first. And then the query selects the 2 newest dates. Am I going to have to manually enter the ID field so that the SELECT TOP will give me the 2 oldest dates?

[CJ_London]

Order dates by DESC will give you the newest first. You also don't need to order the subquery by workrequestID since only the one workrequestid is 'in play' from your criteria. Try

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate
 FROM tblWorkReqInsp
 WHERE InspectionHistoryID In (SELECT TOP 2 InspectionHistoryID                            
   FROM tblWorkReqInsp AS Dupe                              
   WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID        
   ORDER BY Dupe.InspectionDate)
 ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[orange]

I copied your data and imported it into Access, and added an autonumber ID to get the following.

Note: I am in Canada and my regional dates are DD/MM/YYYY format.

ID	WorkRequestID	ComponentClass	InspectionDate	InspectionHistoryID	NetReading	PassFail
1	15567	VALVE	01/05/2015	1446829	49485	FALSE
2	15567	VALVE	01/09/2015	1451761	4	TRUE
3	15568	VALVE	01/05/2015	1446890	6903	FALSE
4	15568	VALVE	01/09/2015	1451762	1871	FALSE
5	15568	VALVE	15/01/2015	1455215	4	TRUE
6	15569	VALVE	01/05/2015	1446916	2540	FALSE
7	15569	VALVE	01/09/2015	1451763	10	TRUE
8	15570	VALVE	01/06/2015	1447819	534	FALSE
9	15570	VALVE	01/08/2015	1450796	182	TRUE
10	15571	VALVE	01/06/2015	1448730	515	FALSE
11	15571	VALVE	01/09/2015	1451627	597	FALSE
12	15571	VALVE	21/01/2015	1459640	2286	FALSE
13	15571	VALVE	21/01/2015	1461034	2282	FALSE
14	15571	VALVE	02/05/2015	1472222	0	TRUE
15	15571	VALVE	17/03/2015	1499486	0	TRUE

2 Newest records per group

SELECT tblWorkReqInsp.WorkRequestID
    ,tblWorkReqInsp.InspectionDate
FROM tblWorkReqInsp
WHERE ID IN (
        SELECT TOP 2 ID
        FROM tblWorkReqInsp AS Dupe
        WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID
        ORDER BY Dupe.WorkRequestID DESC
            ,Dupe.InspectionDate
        )
ORDER BY tblWorkReqInsp.WorkRequestID
    ,tblWorkReqInsp.InspectionDate;

Result:

WorkRequestID	InspectionDate
15567	01/09/2015
15567	01/05/2015
15568	01/09/2015
15568	01/05/2015
15569	01/09/2015
15569	01/05/2015
15570	01/08/2015
15570	01/06/2015
15571	01/09/2015
15571	01/06/2015

2 Oldest records per group

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate
FROM tblWorkReqInsp
WHERE (((tblWorkReqInsp.[ID]) In
        (SELECT TOP 2 ID
                  FROM tblWorkReqInsp AS Dupe
                  WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID
                  ORDER BY Dupe.WorkRequestID DESC
                                         ,Dupe.InspectionDate
             )
))
ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate;

WorkRequestID	InspectionDate
15567	01/05/2015
15567	01/09/2015
15568	15/01/2015
15568	01/05/2015
15569	01/05/2015
15569	01/09/2015
15570	01/06/2015
15570	01/08/2015
15571	21/01/2015
15571	21/01/2015

[CJ_London]

This code should do the whole thing - technically the bit in red is not required because it would return false for datediff>5, but included for clarity

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate, Oldest.InspectionDate, DateDiff("d",Oldest.InspectionDate,tblWorkReqInsp.InspectionDate) AS [Diff>5]
 FROM tblWorkReqInsp 
  INNER JOIN 
    (SELECT WorkRequestID, InspectionDate, InspectionHistoryID FROM tblWorkReqInsp
        WHERE InspectionHistoryID = (SELECT min(InspectionHistoryID)                            
                                                       FROM tblWorkReqInsp AS Dupe                              
                                                         WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID) AS Oldest
     ON tblWorkReqInsp.WorkRequestID=Oldest.WorkRequestID AND tblWorkReqInsp.InspectionHistoryID<>Oldest.InspectionHistoryID
WHERE tblWorkReqInsp.InspectionHistoryID In (SELECT TOP 2 InspectionHistoryID                            
                                                                        FROM tblWorkReqInsp AS Dupe                              
                                                                         WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID        
                                                                           ORDER BY Dupe.InspectionDate)
     AND DateDiff("d",Oldest.InspectionDate,tblWorkReqInsp.InspectionDate)>5
 ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[lbrannon]

This worked for selecting the correct dates! Thank you!

Order dates by DESC will give you the newest first. You also don't need to order the subquery by workrequestID since only the one workrequestid is 'in play' from your criteria. Try

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate
 FROM tblWorkReqInsp
 WHERE InspectionHistoryID In (SELECT TOP 2 InspectionHistoryID                            
   FROM tblWorkReqInsp AS Dupe                              
   WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID        
   ORDER BY Dupe.InspectionDate)
 ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[lbrannon]

I couldn't get this to work. Keeps giving me a syntax error in FROM clause

This code should do the whole thing - technically the bit in red is not required because it would return false for datediff>5, but included for clarity

SELECT tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate, Oldest.InspectionDate, DateDiff("d",Oldest.InspectionDate,tblWorkReqInsp.InspectionDate) AS [Diff>5]
 FROM tblWorkReqInsp 
  INNER JOIN 
    (SELECT WorkRequestID, InspectionDate, InspectionHistoryID FROM tblWorkReqInsp
        WHERE InspectionHistoryID = (SELECT min(InspectionHistoryID)                            
                                                       FROM tblWorkReqInsp AS Dupe                              
                                                         WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID) AS Oldest
     ON tblWorkReqInsp.WorkRequestID=Oldest.WorkRequestID AND tblWorkReqInsp.InspectionHistoryID<>Oldest.InspectionHistoryID
WHERE tblWorkReqInsp.InspectionHistoryID In (SELECT TOP 2 InspectionHistoryID                            
                                                                        FROM tblWorkReqInsp AS Dupe                              
                                                                         WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID        
                                                                           ORDER BY Dupe.InspectionDate)
     AND DateDiff("d",Oldest.InspectionDate,tblWorkReqInsp.InspectionDate)>5
 ORDER BY tblWorkReqInsp.WorkRequestID, tblWorkReqInsp.InspectionDate DESC;

[CJ_London]

well I did type it freehand so may have missed a bracket or something.

I've been through it again and looks like I have missed a bracket here

...
WHERE Dupe.WorkRequestID = tblWorkReqInsp.WorkRequestID)) AS Oldest
...
...