GROUP BY on Last and Second Last record

Question

I have struggled with this for a long time... I am trying to write a SQL Statement in Access to pull out the most recent and Second most recent service dates from a service table called Cleaning.

Sample Raw Data:

Premises_No Date_Cleaned
1           12-Jun
1           15-Jul
1           14-Aug
2           15-Jan
2           18-Feb
2           17-Apr
2           14-May
2           06-Jun
2           11-Jul
2           16-Aug
6           10-Dec
6           12-Jan
6           20-Feb
6           13-Mar
6           15-Apr
6           15-May
6           11-Jun
6           13-Jul
6           10-Aug

So the executed SQL would yield:

Premises_No MostRecent  2ndMostRecent
1           14-Aug          15-Jun
2           16-Aug          11-Jul
6           10-Aug          13-Jul

This submission didnt format the way I wanted it to!!! But just to further clarify: I am trying to produce a report that will give the lastDate and SecondLastDate GROUPED BY Premises_No. Thanks again!! — user1628496, Aug 27 '12 at 19:38
Probably a mix of select premise_id, max(date_cleaned) from cleaning group by premise_id and this http://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value — Martin Wilson, Aug 27 '12 at 20:03

score 0 · Answer 1 · answered Aug 27 '12 at 20:20


SELECT premise_id, SUM(s.[1st]) AS [1st], SUM(s.[2nd]) AS [2nd]
FROM (
    SELECT premise_id, MAX(date_cleaned) AS [1st], NULL AS [2nd] 
    FROM cleaning 
    GROUP BY premise_id
    UNION
    SELECT premise_id, NULL AS [1st], MAX(date_cleaned) AS [2nd] 
    FROM cleaning 
    LEFT JOIN
    (
        SELECT premise_id, MAX(date_cleaned) AS [1st], NULL AS [2nd] 
        FROM cleaning 
        GROUP BY premise_id
    ) AS t ON cleaning.premise_id = t.premise_id and cleaning.date_cleaned  t.date_cleaned
    GROUP BY premise_id
) s

score 0 · Answer 2 · answered Aug 27 '12 at 20:38

SELECT last.Premises_No, last.LastCleaned, secondlast.SecondLastCleaned
FROM
(
SELECT c1.Premises_No as Premises_No, Max(c1.Date_Cleaned) AS LastCleaned
FROM Cleaning c1 group by c1.Premises_No
)
as last
LEFT JOIN
(
SELECT c2.Premises_No as Premises_No, Max(c2.Date_Cleaned) AS SecondLastCleaned
FROM Cleaning c2 where c2.Date_Cleaned < (Select Max(c3.Date_Cleaned) FROM Cleaning c3 WHERE c3.Premises_No= c2.Premises_No)
GROUP BY c2.Premises_No
)
as
secondlast
ON last.Premises_No=secondlast.Premises_No

GROUP BY on Last and Second Last record

2 Answers2