I'm struggling with my query
SELECT * FROM (
SELECT ins.ID, ins.UnitElement_ID, ins.Date, ue.Code,
ROW_NUMBER() OVER (PARTITION BY ins.UnitElement_ID ORDER BY ins.Date DESC) AS lastAnomaly
FROM Inspection ins
INNER JOIN UnitElement ue ON ins.UnitElement_ID = ue.ID
INNER JOIN InspectionedAnomaly ia ON ia.Inspection_ID = ins.ID
WHERE ins.UnitElement_ID IN (3,10)
AND ins.Evaluation IS NOT NULL
) selectedAnomaly
The output result is
ID UnitElement_ID Date Code lastAnomaly
0 3020217 3 2020-10-30 12:09:50 F01001G2 1
1 3020217 3 2020-10-30 12:09:50 F01001G2 2
2 3020217 3 2020-10-30 12:09:50 F01001G2 3
3 3009055 10 2020-05-04 00:00:00 F01001M1 1
4 3009055 10 2020-05-04 00:00:00 F01001M1 2
5 3020224 10 2020-05-04 00:00:00 F01001M1 3
6 3020224 10 2020-05-04 00:00:00 F01001M1 4
7 670231 10 2019-07-23 00:00:00 F01001M1 5
8 670231 10 2019-07-23 00:00:00 F01001M1 6
9 576227 10 2018-11-05 00:00:00 F01001M1 7
When i add the Where clause WHERE lastAnomaly = 1
it works pretty fine, but the problem happens when i have the same exact date as "most recent" date (for example rows 0,1 and 2).
Is there a way, if the most recent date is the same, to extract all 3 rows inside the sql query?
Thank you everyone
Use rank()
and filtering:
SELECT *
FROM (SELECT ins.ID, ins.UnitElement_ID, ins.Date, ue.Code,
RANK() OVER (PARTITION BY ins.UnitElement_ID ORDER BY ins.Date DESC) AS lastAnomaly
FROM Inspection ins JOIN
UnitElement ue
ON ins.UnitElement_ID = ue.ID JOIN
InspectionedAnomaly ia
ON ia.Inspection_ID = ins.ID
WHERE ins.UnitElement_ID IN (3, 10) AND
ins.Evaluation IS NOT NULL
) sa
WHERE lastAnomaly = 1;
Or if you prefer, you can use MAX()
:
MAX(ins.DATE) OVER (PARTITION BY ins.UnitElement_ID) AS lastAnomalyDate
. . .
WHERE lastAnomalyDate = DATE