Get HIGHEST and SECOND HIGHEST value for each ID (SQL)

Question

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:

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

Desired Result

I am in Access 2016, I believe Microsoft version is called T-SQL? — Erik J Lundberg, Feb 06 '19 at 23:38

score 3 · Accepted Answer · answered Feb 06 '19 at 23:40

3

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

answered Feb 06 '19 at 23:40

Michael Ostrovsky

629
1
7
21

Thanks! Used a variation as above to make it work out. – Erik J Lundberg Feb 07 '19 at 00:07
If you've found my answer satisfactory please don't forget to accept it as the answer to this question :) – Michael Ostrovsky Feb 07 '19 at 00:13

MladenB · Answer 2 · 2019-02-07T00:23:32.650

You can try this:

WITH T1
AS ( SELECT   EQP, MAX(Date) COL2
     FROM     dbo.T_TEST
     GROUP BY EQP )
SELECT   B.EQP, B.Date, MAX(A.Date)
FROM     dbo.T_TEST A
         JOIN T1 B ON B.EQP = A.EQP
WHERE    A.Date < B.Date
GROUP BY B.EQP, B.Date;

or if Access not supported CTE

SELECT   B.EQP, B.Date, MAX(A.Date)
FROM     dbo.T_TEST A
         JOIN ( SELECT   EQP, MAX(Date) COL2
                FROM     dbo.T_TEST
                GROUP BY EQP ) B ON B.EQP = A.EQP
WHERE    A.Date < B.Date
GROUP BY B.EQP, B.Date;

score 0 · Answer 3 · answered Feb 07 '19 at 04:55

0

For Access - try this

Select x.EQP, Max(x.Date) as NewDate , Max(y.Date) as PreviousDate from Maintenance as x 
INNER JOIN Maintenance as y ON x.EQP = y.EQP where x.Date > y.Date
group by x.EQP

answered Feb 07 '19 at 04:55

Santosh

12,175
4
41
72

score 0 · Answer 4 · edited Jun 27 '20 at 23:13

How to solve above better with window function? My code is not elegant

select * from
(
select programno  , programdate, a.drk_date--, case when drk_date =1 then programDate 
from
(
select  programNo , programdate, DENSE_RANK() over (partition by programNo order by programdate desc) drk_date 
from program
)a
where a.drk_date <=2
)b
inner join
(
select programno  , programdate, drk_date--, case when drk_date =1 then programDate 
from
(
select  programNo , programdate, DENSE_RANK() over (partition by programNo order by programdate desc) drk_date 
from program
)c
where c.drk_date <=2
)d 
on b.programNo = d.programNo and b.drk_date < d.drk_date

This looks like a question, please ask a question in a new post instead of posting it as an answer — tHeSiD, Jun 27 '20 at 23:14

Get HIGHEST and SECOND HIGHEST value for each ID (SQL)

4 Answers4