SQL Server - ROW_NUMBER() with PARTITION, how to get multiple records?

Question

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

Yes, use `RANK` or `DENSE_RANK` instead of `ROW_NUMBER` then. — Thorsten Kettner, Oct 30 '20 at 11:36
When tagging, please only tag the RDBMS you are *really* using. MySQL and SQL Server are 2 completely different RDBMS — Thom A, Oct 30 '20 at 11:39

score 1 · Accepted Answer · answered Oct 30 '20 at 11:37

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

score 1 · Answer 2 · answered Oct 30 '20 at 11:37

Use rank() instead of row_number(). It assigns the same rank to rows that are ties as regard to the ORDER BY of the OVER() clause.

So:

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
   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
WHERE lastAnomaly = 1

SQL Server - ROW_NUMBER() with PARTITION, how to get multiple records?

2 Answers2