finding the most recently updated record with multiple date columns

Question

I have recently inherited a SQL Server 2016 database which was converted from an old MS Access db. I have a table that shows various repair dates and test dates for associated serial numbers. I would like to have a single row per serial number with all of the most recent dates shown. The table right now is showing multiple rows per serial number depending on the various dates entered into the database.

Repair and Config Date Table:

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | NULL        | NULL            | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | NULL        | NULL            | NULL          |
| 1004 | NULL        | 2017-03-28      | 2017-09-07    |
| 1004 | 2017-12-15  | NULL            | NULL          |
+------+-------------+-----------------+---------------+

Desired Output

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | 2017-12-15  | 2017-03-28      | 2017-09-07    |
+------+-------------+-----------------+---------------+

How can I combine the table results so only one row of serial number is showing the latest dates from each column?

Answer 1

You can get the aggregates, MAX(Date) in this case, by using GROUP BY:

_{Quoted from MS Docs:}

_{A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.}

SELECT
    SerialNumber,
    MAX(Repair_Date) as Repair_Date,
    MAX(Inspection_Date) as Inspection_Date,
    MAX(Replaced_Date) as Replaced_Date 
FROM
    YourTable
GROUP BY 
    SerialNumber