New to SQL. In Access 2016. In a table of different equipment (EQUIP1, EQUIP2, EQUIP3), I want in a query the last and second to last maintenance dates.
Many fixes I have searched for do not account for grouping by an ID (EQUIP# in my case)
What I have:
x
TABLE Maintenance
equipment Date
1 1/1/2019
1 1/2/2019
1 1/3/2019
2 2/1/2019
2 2/2/2019
2 2/3/2019
What I need:
QUERY LATESTDATES
equipment NewDate PreviousDate
1 1/3/2019 1/2/2019
2 2/3/2019 2/2/2019
EDIT: Thanks! Got a little caught up on syntax, but here’s my final solution:
SELECT [a1].equipment, NewDate, Max([b].Date) as PreviousDate
FROM
(SELECT equipment,Max(Date) as NewDate
FROM Maintenance AS [A]
GROUP BY equipment) AS [a1]
INNER JOIN Maintenance AS [b]
ON [b].equipment= [a1].equipment AND [b].Date <> [a1].NewDate
GROUP BY [a1].equipment, [a1].NewDate
Advertisement
Answer
This is my solution, it might not be the cleanest but it should work in any SQL.
select a1.equipment, highest_date, max(b.date) as second_highest_date
from
(
select equipment, max(date) as highest_date
from YOUR_TABLE as a
group by equipment
) a1
join YOUR_TABLE as b
on b.equipment = a1.equipment and b.date != a1.highest_date
group by a1.equipment, a1.highest_date