Filter SELECT results on single column

Question

I am working with a 'Route' table, which details an ordered set of 'Stages' for a particular ProductID:

| ID | ProductID | StageID | Order |
|----+-----------+---------+-------|
|  0 |         1 |       4 |     1 |
|  1 |         1 |       2 |     2 |
|  2 |         1 |       3 |     3 |
|  3 |         1 |       7 |     4 |
|  4 |         1 |       5 |     5 |
|----+-----------+---------+-------|

And a Records table, which details timestamped PASS/FAIL results for a product Serial Number at a specific Stage:

| ID | RecordDate          | Serial Number | StageID | Result |
|----+---------------------+---------------+---------+--------|
|  0 | 2019-03-16 00:00:00 | G001          |       4 |      1 |
|  1 | 2019-03-16 00:01:00 | G001          |       2 |      1 |
|  2 | 2019-03-16 00:02:00 | G001          |       2 |      0 |
|----+---------------------+---------------+---------+--------|

You can see from the above hypothetical data that Serial Number G001 has been through Stage 4, and PASSED, then Stage 2 and PASSED, but then it went through Stage 2 again and FAILED.

Now, I want to select results from this record table for a specific SerialNumber where the StageID exists in the Route for that product. That's easy - here's my query:

SELECT StageID, Result, RecordDate
FROM Records
WHERE StageID IN (SELECT StageID FROM Route WHERE ProductID = 1) AND SerialNumber = 'G001';

For the above data, this gives me:

| StageID | Result | RecordDate          |
|---------+--------+---------------------|
|       4 |      1 | 2019-03-16 00:00:00 |
|       2 |      1 | 2019-03-16 00:00:01 |
|       2 |      0 | 2019-03-16 00:00:02 |
|---------+--------+---------------------|

Now, the complicated bit where my knowledge of SQL unfortunately expires is that I want to filter on the StageID, so that only the most recent result in the Records table for that StageID and SerialNumber is shown. So in the given example, I want this:

| StageID | Result | RecordDate          |
|---------+--------+---------------------|
|       4 |      1 | 2019-03-16 00:00:00 |
|       2 |      0 | 2019-03-16 00:00:02 |
|---------+--------+---------------------|

The filtering could be done on RecordDate or, more easily again, on Records.ID.

But I can't figure out the SQL that would achieve this and I think it's a case of me not knowing enough of the language to articulate what I'm trying to do.

So my question: Is it possible to filter these results on the most recent Record for each StageID?

I have played with GROUP BY options to no avail.

Answer 1

WITH cte 
     AS (SELECT StageID, 
                Result, 
                RecordDate, 
                Row_number() 
                  OVER( 
                    partition BY StageID 
                    ORDER BY Record_Date DESC) AS rn 
         FROM   table) 
SELECT StageID, 
       Result, 
       RecordDate 
FROM   cte 
WHERE  rn = 1 
ORDER  BY StageID DESC 

Answer 2

Use window functions:

SELECT r.StageID, r.Result, r.RecordDate
FROM (SELECT r.*
             ROW_NUMBER() OVER (PARTITION BY r.StageID ORDER BY r.RecordDate DESC) as seqnum
       FROM Records r
     ) JOIN
     Route ro
     ON ro.StageID = r.StageID AND ro.ProductId = 1
WHERE r.SerialNumber = 'G001';

Or, you can add

SELECT r.StageID, r.Result, r.RecordDate
FROM Records r
WHERE r.StageID IN (SELECT ro.StageID
                    FROM Route ro
                    WHERE ro.ProductID = 1
                   ) AND
      r.SerialNumber = 'G001' AND
      r.RecordDate = (SELECT MAX(r2.RecordDate)
                      FROM records r2
                      WHERE r2.StageId = r.StageId
                     );